LastDate filter problem

Hi fjjpeeters1976 

Great question, I’ve run into this challenge before, especially when working with non-additive measures like balance sheet values that need to be pulled from a specific date (like year-end). You're on the right track using LASTDATE() the main issue comes from how you're applying the year filter. This line,

Test = CALCULATE(LASTDATE('Date'[Date]), 'Date'[Year] = 2024)

throws an error because DAX doesn't accept boolean expressions directly like that inside CALCULATE.

Here's a working approach that will let you restrict LASTDATE to just 2024.

Test 1 = CALCULATE(LASTDATE('Date'[Date]),FILTER(ALL('Date'), 'Date'[Year] = 2024))

This tells DAX to look at all dates (ignoring any visual filters), and then limit it to dates in 2024 before returning the last one. It's the safest way to apply a year filter inside a CALCULATE.

Then apply it inside your measure like this.

VAR lastDate2024 = CALCULATE(LASTDATE('Date'[Date]),FILTER(ALL('Date'), 'Date'[Year] = 2024))

And replace your original LASTDATE(...) lines with lastDate2024.

If you plan to make this dynamic in the future (e.g., based on a slicer), just define a selected year like this:

VAR SelectedYear = SELECTEDVALUE('Date'[Year], 2024)

And update the filter to.

FILTER(ALL('Date'), 'Date'[Year] = SelectedYear)

Hope this helps make your allocation factor work well with balance sheet items. 

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Thanks,
Akhil.