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.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 565 Datasets
 2K Magic ETL
 3.4K Visualize
 2.3K Charting
 593 Beast Mode
 13 App Studio
 28 Variables
 588 Automate
 143 Apps
 417 APIs & Domo Developer
 27 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 361 Distribute
 99 Domo Everywhere
 260 Scheduled Reports
 2 Software Integrations
 96 Manage
 93 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive