Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chamue329
Helper II
Helper II

Running 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

 

Running Total Tables.JPG

Thanks in advance.

10 REPLIES 10
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
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/

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])))

@chamue329 

Can you share sample data and sample output. I will try to create pbix

@amitchandakSorry I don't know how to attach a data file, so instead I pasted the sample data below the screenshot.  The desired result is the value 641, which is the aggregate of the latest date 4/8/20 values for Uzbekistan 548, US 93 (made up of the US counties Abbeville SC = 5 and Acadia LA = 88). I want to link the output value to a Card.

 

  1. Example List.JPG

Country_RegionProvince_StateCombined_KeyDateCases

UzbekistanN/A 4/1/2020 0:00183
UzbekistanN/A 4/2/2020 0:00207
UzbekistanN/A 4/3/2020 0:00229
UzbekistanN/A 4/4/2020 0:00268
UzbekistanN/A 4/5/2020 0:00344
UzbekistanN/A 4/6/2020 0:00459
UzbekistanN/A 4/7/2020 0:00522
UzbekistanN/A 4/8/2020 0:00548
USSouth CarolinaAbbeville, South Carolina, US3/31/2020 0:004
USSouth CarolinaAbbeville, South Carolina, US4/1/2020 0:004
USSouth CarolinaAbbeville, South Carolina, US4/2/2020 0:006
USSouth CarolinaAbbeville, South Carolina, US4/3/2020 0:006
USSouth CarolinaAbbeville, South Carolina, US4/4/2020 0:006
USSouth CarolinaAbbeville, South Carolina, US4/5/2020 0:006
USSouth CarolinaAbbeville, South Carolina, US4/6/2020 0:006
USSouth CarolinaAbbeville, South Carolina, US4/7/2020 0:005
USSouth CarolinaAbbeville, South Carolina, US4/8/2020 0:005
USLouisianaAcadia, Louisiana, US3/31/2020 0:0040
USLouisianaAcadia, Louisiana, US4/1/2020 0:0048
USLouisianaAcadia, Louisiana, US4/2/2020 0:0062
USLouisianaAcadia, Louisiana, US4/3/2020 0:0073
USLouisianaAcadia, Louisiana, US4/4/2020 0:0067
USLouisianaAcadia, Louisiana, US4/5/2020 0:0077
USLouisianaAcadia, Louisiana, US4/6/2020 0:0081
USLouisianaAcadia, Louisiana, US4/7/2020 0:0084
USLouisianaAcadia, Louisiana, US4/8/2020 0:0088

 

thank you.

 

 

This one should. But this will also filter last date for every country in visual

Measure =
VAR __id = MAX ( 'Table'[country_region] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[country_region] = __id )
RETURN CALCULATE ( sum ( 'Table'[cases] ), VALUES ( 'Table'[country_region] ), 'Table'[country_region] = __id, 'Table'[date] = __date )

Hi @chamue329 ,

Please try to create a measure as below:

Measure = CALCULATE(SUM('Table'[Cases]),FILTER('Table','Table'[Date]=MAX('Table'[Date])))

total.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msftI tried your measure and get a "[blank]" for my result.  I'm not sure what I'm doing wrong.  Here is my script:

 

Latest Tot Cases = CALCULATE(SUM('Global Time Series'[Cases]),FILTER('Calendar_B','Calendar_B'[Date]=MAX('Calendar_B'[Date])))

Hi @chamue329 ,

Could you please provide the screen shot with structures of table Global Time Series and Calendar_B and some sample data? It needs to include field names and existed relationship between these two tables just like as below screen shot. It is better if you can share your PBIX file by uploading to OneDrive for Business.

sample data.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi@amitchandak Since the running totals are already cumulative, will this overstate my result?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.