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.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 600 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 685 Beast Mode
 43 App Studio
 38 Variables
 656 Automate
 170 Apps
 439 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 385 Distribute
 110 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 25 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive