cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User IV
Super User IV

Re: Running total value from latest date with multiple category groups

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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Running total value from latest date with multiple category groups

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

Highlighted
Helper II
Helper II

Re: Running total value from latest date with multiple category groups

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

Highlighted
Super User IV
Super User IV

Re: Running total value from latest date with multiple category groups

@chamue329 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Running total value from latest date with multiple category groups

@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.

 

 

Highlighted
Microsoft
Microsoft

Re: Running total value from latest date with multiple category groups

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

Highlighted
Helper II
Helper II

Re: Running total value from latest date with multiple category groups

@yingyinrI 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])))
Highlighted
Microsoft
Microsoft

Re: Running total value from latest date with multiple category groups

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

Highlighted
Super User IV
Super User IV

Re: Running total value from latest date with multiple category groups

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 )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors