Forum Discussion
Helper IIRunning total value from latest date with multiple category groups
Super UserCan you share sample data and sample output. I will try to create pbix
Helper IIamitchandakSorry 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.
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.
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])))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
- 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 )