Forum Discussion

matthias_vc's avatar
matthias_vc
Frequent Visitor
6 years ago
Solved

Linear Interpolation between dates

Hi I'm trying to do a measure called "Earned Schedule" which basically interpolates some value (EV) in a lookuptable and returns the Date we should have reached this value (and then displays it in Elapsed days) 

This should be the formula

 

ES = 
VAR LowerPV = CALCULATE(MAX('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]<[EV]))
VAR HigherPV = CALCULATE(MIN('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]>[EV]))
VAR LowerDate = CALCULATE(MAX('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]<[EV]))
VAR HigherDate = CALCULATE(MIN('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]>[EV]))
RETURN LowerDate-[StartDate] + ([EV]-LowerPV)/(HigherPV-LowerPV)*(HigherDate-LowerDate)

 

 however though I do get the correct result. It interprets it as a date instead of a number.
It displays 3/10/1900, 7:53:30 where it should be 70,32867.

If I try to return a different part it usually shows correctly, unless I'm deducting Dates.

Return StartDate1/1/2020 12:00:00 AMReturn HigherDate-LowerDate1/30/1900 12:00:00 AM
Return LowerDate3/1/2020 12:00:00 AMFull Formula3/10/1900, 7:53:30 AM
Return HigherDate4/1/2020 12:00:00 AM  
Approximate Lookup Dates
  • matthias_vc's avatar
    matthias_vc
    6 years ago

    Hi, 
    I did check the Format, but if Power BI thinks it's a date, you're only allowed to choose between Date Formats.

    In the meantime I did figure it out. Apparently Power BI doesn't always see the deduction of two Dates as a number.
    To force it there is apparently a trick (multiplying by 1). So I rewrote my DAX to this:

    ES = 
VAR _EV = [EV]
VAR LowerPV = CALCULATE(MAX('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]<_EV))
VAR HigherPV = CALCULATE(MIN('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]>_EV))
VAR LowerDate = CALCULATE(MAX('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]<_EV))
VAR HigherDate = CALCULATE(MIN('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]>_EV))
RETURN (LowerDate-[StartDate])*1 + (_EV-LowerPV)/(HigherPV-LowerPV)*(HigherDate-LowerDate)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI matthias_vc ,

     

     

    Change the format.

     

     

     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • matthias_vc's avatar
      matthias_vc
      Frequent Visitor
      6 years ago

      Hi, 
      I did check the Format, but if Power BI thinks it's a date, you're only allowed to choose between Date Formats.

      In the meantime I did figure it out. Apparently Power BI doesn't always see the deduction of two Dates as a number.
      To force it there is apparently a trick (multiplying by 1). So I rewrote my DAX to this:

      ES = 
VAR _EV = [EV]
VAR LowerPV = CALCULATE(MAX('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]<_EV))
VAR HigherPV = CALCULATE(MIN('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]>_EV))
VAR LowerDate = CALCULATE(MAX('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]<_EV))
VAR HigherDate = CALCULATE(MIN('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]>_EV))
RETURN (LowerDate-[StartDate])*1 + (_EV-LowerPV)/(HigherPV-LowerPV)*(HigherDate-LowerDate)