Linear Interpolation between dates

Question

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)&nbsp;This should be the formula&nbsp;ES = 
VAR LowerPV = CALCULATE(MAX('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]&lt;[EV]))
VAR HigherPV = CALCULATE(MIN('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]&gt;[EV]))
VAR LowerDate = CALCULATE(MAX('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]&lt;[EV]))
VAR HigherDate = CALCULATE(MIN('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]&gt;[EV]))
RETURN LowerDate-[StartDate] + ([EV]-LowerPV)/(HigherPV-LowerPV)*(HigherDate-LowerDate)&nbsp;&nbsp;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 AMReturn LowerDate3/1/2020 12:00:00 AMFull Formula3/10/1900, 7:53:30 AMReturn HigherDate4/1/2020 12:00:00 AM&nbsp;&nbsp;

matthias_vc · Accepted Answer

Hi,&nbsp;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]&lt;_EV))
VAR HigherPV = CALCULATE(MIN('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]&gt;_EV))
VAR LowerDate = CALCULATE(MAX('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]&lt;_EV))
VAR HigherDate = CALCULATE(MIN('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]&gt;_EV))
RETURN (LowerDate-[StartDate])*1 + (_EV-LowerPV)/(HigherPV-LowerPV)*(HigherDate-LowerDate)

Forum Discussion

Related content

Linear

Fill blanks with linear interpolation

Linear interpolation DAX measure

Linear Interpolation of Multiple Data Series

Linear Interpolation with a lookup table