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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive