Forum Discussion

Spike_Craib's avatar
Spike_Craib
Regular Visitor
4 years ago
Solved

Problems with IF when comparing a Column Value to a Measure

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?
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    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!

2 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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!

  • Spike_Craib's avatar
    Spike_Craib
    Regular Visitor
    4 years ago

    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.