Forum Discussion
Sum LASTDATE Total Issue
I am having trouble getting my totals to calculate correctly for the example below in which a sum filtering by LASTDATE is used. I understand the measure is calculating on the total line, so that's why it's producing an undesired result, but I haven't been able to figure out how to make the total correct.
In the example below, my page has a filter where filter column = Y (so the 4/30/2024 row for item A is excluded). The desire is to show the value from the value column for each item where the date is the largest within each item. So, for A, it should show a value of 3 from the 3/31/2024 record and for B, it should show a value of 4 from the 4/30/2024 record. The totals row should add these two values together for a total of 7. The DAX below is working correctly for each row, but the total is showing 4 (which it is picking up from the 4/30/2024 B record). How can I make this total show 7?
Measure = CALCULATE(SUM('Data'[Value]), LASTDATE('Data'[Date]))
Data:
| Item | Value | Filter Column | Date |
| A | 1 | Y | 1/31/2024 |
| A | 2 | Y | 2/29/2024 |
| A | 3 | Y | 3/31/2024 |
| A | 3 | N | 4/30/2024 |
| B | 1 | Y | 1/31/2024 |
| B | 2 | Y | 2/29/2024 |
| B | 3 | Y | 3/31/2024 |
| B | 4 | Y | 4/30/2024 |
Expected Result:
| Item | Measure | Latest Date |
| A | 3 | 3/31/2024 |
| B | 4 | 4/30/2024 |
| Total | 7 | --- |
Current Result:
| Item | Measure | Latest Date |
| A | 3 | 3/31/2024 |
| B | 4 | 4/30/2024 |
| Total | 4 | --- |
You may have to use the SUMX function for this.
If you don't have an existing table where your values are equal to your measure, then consider the following:
Measure = VAR _SumTable = Summarize( Data[Item], "Measure",CALCULATE(Data[Value],LASTDATE(Data[Date]) ) RETURN SUMX(_SumTable, [Measure] )
3 Replies
ExcelMonkeImpactful Individual
You may have to use the SUMX function for this.
If you don't have an existing table where your values are equal to your measure, then consider the following:
Measure = VAR _SumTable = Summarize( Data[Item], "Measure",CALCULATE(Data[Value],LASTDATE(Data[Date]) ) RETURN SUMX(_SumTable, [Measure] )I stumbled across this after I posted and I think it's basically saying the same thing. I think I have it working using a similar measure.
Measure Totals, The Final Word - Microsoft Fabric Community- ExcelMonke
Great!
