Problems with IF when comparing a Column Value to a Measure

[Spike_Craib]

Hi - Any help is gratefully appreciated. I've been looking at this problem for a few hours now and am stumped.

The Position

I have the following measures set to a format of Short Date

FY End = LASTDATE(CalendarLookup[Calendar_Date])

FY Start = FIRSTDATE(CalendarLookup[Calendar_Date])

CalendarLookup is an XLSX doc table.

I have a table called MaintenanceForecast (also an XLSX doc table) One of the fields in this table is called End Date. This is set as a Date in Short Date format.

Inside Power BI Desktop I have added a New Column to MaintenanceForecast

This Year End = IF(MaintenanceForecast[End Date] > [FY End], [FY End], IF(MaintenanceForecast > [FY Start], MaintenanceForecast[End Date],DATE(1066,1,1)))

(N.B. - The 1066 date is just a placeholder for now)

The Problem

The IF state never matches the first condition, no matter the date.

I have one entry that has an End Date of 26/06/2022 and a FY End of 27/02/2022. Now I may be getting old but the first date is greater than the second date; but I never get the correct answer.

I've done some troubleshooting and if I add a new column to my MaintenanceForecast table and manually add a value for FY End date then it works fine.

The problem is only when comparing a date from a table column versus a date from a measure.

I've even tried converting the column and measure dates to integers and comparing them. Same problem.

Now I'm stumped. Any suggestions?

[Spike_Craib]

Hi @ValtteriN 

Thanks for the reply. I don't have a relationship between the tables. I did try the ALL but it didn't solve my problem.

However, your suggestion of moving the measure into a calculated column did work. I moved it into the MaintenanceForecast table and that problem has now been fixed. Thank you so much for the suggestion.

[ValtteriN · Accepted solution]

Hi,

Do you happen to have a relationship between your calendar table and maintance forecast? If you reference measures in a calculated column they will be evaluated in the columns filter context. and thus they will return the date used in the realtionship. If you add ALL to your end and start measures your calculations should work. This picture might elaborate what I mean (here I used LASTDATE measure and placed it in a calculated column): 

FY End = CALCULATE(LASTDATE(CalendarLookup[Calendar_Date]),ALL(CalendarLookup[)) Should work.

I hope this helps and if it does consider accepting this post as a solution and giving it a thumbs up!