Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
matthias_vc
Frequent Visitor

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  
1 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)

View solution in original post

2 REPLIES 2
harshnathani
Community Champion
Community Champion

HI @matthias_vc ,

 

 

Change the format.

 

1.jpg

 

 

Regards,
Harsh Nathani

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

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)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.