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 
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?

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

      Only for those dates that are on there. The dates will be sporadic, but all I am trying to do is fill in the gaps for the missing ones i.e. draw a direct across the top of the known ones on the graph and fill up to that line for the missing dates.

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

        Hi justlogmein 
        You can create a date table and use it for slicing. A measure will be more practical than a calculated column https://www.dropbox.com/t/fEcL3Hmb9Ob0ea3Q

        Engine Hours:=VAR CurrentValue = MAX ( 'Sample Data'[Engine Meter Reading - Valid Only] )
VAR MaxDateWithData = CALCULATE ( MAX ( 'Sample Data'[Sample Date] ), ALL ( 'Calendar' ) )
VAR CurrentDate = MAX ( 'Calendar'[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
VAR Result =
	IF ( 
		MaxDateWithData >= CurrentDate,
		IF ( ISBLANK ( CurrentValue ), PreviousValue + DIVIDE ( ValueDifference, DateDifference1 ) * DateDifference2, CurrentValue )
	)
RETURN
	Result