Sum LASTDATE Total Issue

[mgantt87]

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

[ExcelMonke · Accepted solution]

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

[mgantt87]

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!