Forum Discussion
Helper IIRunning total value from latest date with multiple category groups
Hi, apologies if this solution exists out there. I spent some time going through various posts but couldn 't find a solution. I have a time series table grouped by Country, Province and Combined Key (County & State) with a running total grouped by Combined Key. I want to set up a card that displays the running total value from the latest data across the entire table (aggregated).
Here's a a snapshot of my table and the desired result highlighted in yellow. For the "Latest Tot Cases" it should be 1.6M and so on. I thought one solution is to create a calculated column titled Latest Value that indexes by Combined
Thanks in advance.
10 Replies
- amitchandak
Super User
Are you looking for a cumulative Total? Try with date calendar
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
chamue329Hiamitchandak Since the running totals are already cumulative, will this overstate my result?
- chamue329
Hi amitchandak can you help clarify a couple of questions?
1) Are both of these measures supposed to titled the same?
2) Can you help me understand the difference between the two in terms of what they're intended to do? The first one has a "maxx" and references the calendar table, while the second has only a "max" and references the Sales Date. When completing the <=max(Sales[Sales Date] I get an error that says "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))- amitchandak
Can you share sample data and sample output. I will try to create pbix
Hi chamue329 ,
Whether the problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.
Best Regards
Rena
