Forum Discussion

justlogmein's avatar
justlogmein
Icon for Helper III rankHelper III
4 years ago
Solved

How to interpolate data that is in groups to estimate missing values?

I am currently doing an engine oil analysis for mining equipment and I have noticed that many of the engine hour readings on the oil sample cards are incorrect. Some are obvious outliers, but others ...
DAX
interpolation
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi justlogmein 
    Actually I have double checked. There is no problem with the interpolation. It is 100% accurate but the thing is you the intervals in the x-axis are not uniform which makes the chart looks odd. I think you need to proceed with a different aproach uniforming the intervals at the x-axis. 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi justlogmein 
Here is the sample file with the solution https://www.dropbox.com/t/iRbNBQW2Jfh0ylaw

=
VAR CurrentValue = [Engine Meter Reading - Valid Only]
VAR CurrentDate = 'Sample Data'[Sample Date]
VAR CurrentUnitTable = FILTER ( CALCULATETABLE ( 'Sample Data', ALLEXCEPT ( 'Sample Data', 'Sample Data'[Unit Number] ) ), [Engine Meter Reading - Valid Only] <> BLANK ( ) )
VAR DatesBefore = FILTER ( CurrentUnitTable, 'Sample Data'[Sample Date] < CurrentDate )
VAR DatesAfter = FILTER ( CurrentUnitTable, 'Sample Data'[Sample Date] > CurrentDate )
VAR PreviousDate = MAXX ( DatesBefore, 'Sample Data'[Sample Date] )
VAR NextDate = MINX ( DatesAfter,  'Sample Data'[Sample Date] )
VAR DateDifference1 = DATEDIFF ( PreviousDate, NextDate, DAY ) 
VAR DateDifference2 = DATEDIFF ( PreviousDate, CurrentDate, DAY ) 
VAR PreviousValue = MAXX ( FILTER ( DatesBefore, 'Sample Data'[Sample Date] = PreviousDate ), 'Sample Data'[Engine Meter Reading - Valid Only] )
VAR NextValue = MAXX ( FILTER ( DatesAfter, 'Sample Data'[Sample Date] = NextDate ), 'Sample Data'[Engine Meter Reading - Valid Only] )
VAR ValueDifference = NextValue - PreviousValue
RETURN
	IF ( ISBLANK ( CurrentValue ), PreviousValue + DIVIDE ( ValueDifference, DateDifference1 ) * DateDifference2, CurrentValue )
justlogmein's avatar
justlogmein
Icon for Helper III rankHelper III
4 years ago

Thank you Tamer. I see that there are some large jumps in the hours in the output there. I would have expected the interpolated data (for missing values) to be half the difference of the two two known numbers on each side of it, so essentially a straight line between between the known ones. I'm wondering if the multiple missing values in a row is affecting the calculation?

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi justlogmein 
    Actually I have double checked. There is no problem with the interpolation. It is 100% accurate but the thing is you the intervals in the x-axis are not uniform which makes the chart looks odd. I think you need to proceed with a different aproach uniforming the intervals at the x-axis. 

    • justlogmein's avatar
      justlogmein
      Icon for Helper III rankHelper III
      4 years ago

      Thank you. Would you happen to know how I could do this in DAX? I could easily interpolate the data, even if not uniform, in Excel, but I am not that experienced with DAX to know what formulas to use.

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        justlogmein 
        What is the time interval that you're looking for?