Question: 'Days Out' Measure not interacting with Date Table as expected

[KBrez]

Hi All,

I am building a model that compares data year-over-year against an event date that changes each year. I pulled a solution off a forum, and created a calculated column in my date table that compares each row to the Event Date:

I added a 'DateFilter' to constrain the data in visuals, simplify YoY comparative metrics, etc. Initially, I tried to do this with a Measure based on Max purchase date and the event date:

CurrentDaysOut = Max('factDonorsSurveyResponses_28896 (6)'[PurchaseDate]) - Date(2023,05,07)

But the filter column using the measure doesn't behave as expected: 

The solution I came up with was to recreate the measure as a variable in the calculated column: 

My question is: why doesn't using the Measure in the calculated column expression work? As written above, the measure is calculated as a date. I was able to convert it to a whole number by putting it inside Value() and DateValue() functions, or rewriting it as a DATEDIFF function; this generated a different, but still incorrect, results. The only thing that worked was hardcoding the Measure's value e.g.:  CURRENTDAYSOUT= -32. So in theory there must be a way to get the Measure to work in this context. The variable in the calculated column is basically a copy of the measure, and it works. I am still pretty green with Dax, so any perspective would be appreciated.

[Greg_Deckler · Accepted solution]

@KBrez Columns are not dynamic. They are calculated at the time of data load/refresh.

[KBrez]

Thanks @Greg_Deckler . So as a general rule should I avoid referencing Measures in Calculated Columns? 

[Greg_Deckler]

@KBrez Correct. 99 times out of a 100 it isn't going to work like you want.