Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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])))
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.
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.
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])))
Best Regards
Rena
@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:
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
Hi@amitchandak Since the running totals are already cumulative, will this overstate my result?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |