How to Handle Ending Balances

calvert_schaefer

Looking for help understanding how to handle ending balances in Domo. I have a customer metric called Doors that records how many retail centers are carrying the product. I mocked up a dataset which is attached but there are two main columns of interest, the date column and the Door Count.

Doors is a running total so the default sum method when trying to graph by week, month, quarter, year does not apply. The graph is correct when I graph by day but when graphing by any summary element of time it is adding up the daily totals when I need it to show only the maximum of date in scope of said week, month, quarter, year.

How do I achieve this in Domo?

Here is a screenshot of the desired results in PowerBI with an Ending Balance Measure

EB = CALCULATE(sum(Sheet1[Doors]),'Sheet1'[Date]=MAX('calendar'[Date]))

Here is what Domo is doing

doors sample.xlsx

jessdoe

I don't think you need a formula, just chose the "max" option when you click the drop down menu on the "doors" column directly in your bar chart in Domo.

marcel_luthi

If the rolling total always goes up (like you don't have centers discontinuing the product or closing) then the max alternative will work just fine. Should this not be the case, my approach would be to use an ETL to obtain the daily change and have that one as an additional column (the only problem you'll have is that on the first day of your dataset you'll be moving from 0 to whatever your first Door number is). The ETL would look something like:

Then do a Formula field that calculates the difference between Doors and Previous Doors and save that to a new dataset.

Then you can graph by the SUM of Daily Doors instead.

GrantSmith

Graphing by week or month would cause duplicate records to display with the window function because the date slice is different than what you’re ordering by.

jessdoe

I don't think you need a formula, just chose the "max" option when you click the drop down menu on the "doors" column directly in your bar chart in Domo.

marcel_luthi

If the rolling total always goes up (like you don't have centers discontinuing the product or closing) then the max alternative will work just fine. Should this not be the case, my approach would be to use an ETL to obtain the daily change and have that one as an additional column (the only problem you'll have is that on the first day of your dataset you'll be moving from 0 to whatever your first Door number is). The ETL would look something like:

Then do a Formula field that calculates the difference between Doors and Previous Doors and save that to a new dataset.

Then you can graph by the SUM of Daily Doors instead.

calvert_schaefer

@marcel_luthi thanks for providing an example. This solution works however it requires me to always display the time horizon as All Time for the running total of Daily Doors. How would i go about creating the measure that calculates the running total?

My first guess is sum(sum(`Daily Doors`)) over ( order by `Date `) however I'm afraid I will run into the beast mode limitation where since the dataset is by Day, it won't aggregate up to week,month,quarter in a single column.

Here is a link to an article referencing the issue

https://community-forums.domo.com/main/discussion/52681/domo-ideas-conference-beast-modes-running-totals

GrantSmith

Graphing by week or month would cause duplicate records to display with the window function because the date slice is different than what you’re ordering by.