Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
KBrez
Frequent Visitor

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

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:

KBrez_0-1681132816056.png

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: 
KBrez_1-1681134576142.png

 

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

KBrez_2-1681134679716.png

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.