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