Linear Interpolation between dates

[matthias_vc]

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 StartDate	1/1/2020 12:00:00 AM	Return HigherDate-LowerDate	1/30/1900 12:00:00 AM
Return LowerDate	3/1/2020 12:00:00 AM	Full Formula	3/10/1900, 7:53:30 AM
Return HigherDate	4/1/2020 12:00:00 AM

[harshnathani]

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 · Accepted solution]

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)