Running total value from latest date with multiple category groups

[chamue329]

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.

[Anonymous]

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

[amitchandak]

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/

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

@chamue329 

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

[chamue329]

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

Country_RegionProvince_StateCombined_KeyDateCases

Uzbekistan	N/A		4/1/2020 0:00	183
Uzbekistan	N/A		4/2/2020 0:00	207
Uzbekistan	N/A		4/3/2020 0:00	229
Uzbekistan	N/A		4/4/2020 0:00	268
Uzbekistan	N/A		4/5/2020 0:00	344
Uzbekistan	N/A		4/6/2020 0:00	459
Uzbekistan	N/A		4/7/2020 0:00	522
Uzbekistan	N/A		4/8/2020 0:00	548
US	South Carolina	Abbeville, South Carolina, US	3/31/2020 0:00	4
US	South Carolina	Abbeville, South Carolina, US	4/1/2020 0:00	4
US	South Carolina	Abbeville, South Carolina, US	4/2/2020 0:00	6
US	South Carolina	Abbeville, South Carolina, US	4/3/2020 0:00	6
US	South Carolina	Abbeville, South Carolina, US	4/4/2020 0:00	6
US	South Carolina	Abbeville, South Carolina, US	4/5/2020 0:00	6
US	South Carolina	Abbeville, South Carolina, US	4/6/2020 0:00	6
US	South Carolina	Abbeville, South Carolina, US	4/7/2020 0:00	5
US	South Carolina	Abbeville, South Carolina, US	4/8/2020 0:00	5
US	Louisiana	Acadia, Louisiana, US	3/31/2020 0:00	40
US	Louisiana	Acadia, Louisiana, US	4/1/2020 0:00	48
US	Louisiana	Acadia, Louisiana, US	4/2/2020 0:00	62
US	Louisiana	Acadia, Louisiana, US	4/3/2020 0:00	73
US	Louisiana	Acadia, Louisiana, US	4/4/2020 0:00	67
US	Louisiana	Acadia, Louisiana, US	4/5/2020 0:00	77
US	Louisiana	Acadia, Louisiana, US	4/6/2020 0:00	81
US	Louisiana	Acadia, Louisiana, US	4/7/2020 0:00	84
US	Louisiana	Acadia, Louisiana, US	4/8/2020 0:00	88

thank you.

[amitchandak]

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 )

[Anonymous]

Hi @chamue329 ,

Please try to create a measure as below:

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

Best Regards

Rena

[chamue329]

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

[Anonymous]

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.

Best Regards

Rena

[chamue329]

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