How to Handle Ending Balances
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
Best Answers

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.
2 
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
andPrevious Doors
and save that to a new dataset.Then you can graph by the SUM of
Daily Doors
instead.0 
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers

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.
2 
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
andPrevious Doors
and save that to a new dataset.Then you can graph by the SUM of
Daily Doors
instead.0 
@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
0 
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.3K Connect
 1.1K Connectors
 273 Workbench
 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 525 Datasets
 2.1K Magic ETL
 2.9K Visualize
 2.2K Charting
 434 Beast Mode
 22 Variables
 510 Automate
 114 Apps
 388 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 66 Manage
 66 Governance & Security
 1 Product Release Questions
 Community Forums
 40 Getting Started
 26 Community Member Introductions
 67 Community Announcements
 4.8K Archive