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 El...
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)
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)