Forum Discussion
Helper IIIHow 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 are more subtle. All of the readings should stay the same or increase as the date increases, but some dip on the next date or go up on a previous date.
Because I can't work out which of these readings is the incorrect one in all scenarios, I thought it would be best to simply remove any I am not sure about (including the outliers) and then interpolate the readings using the dates as the independent variable. I also need to group these by machine, called Unit Number in the data.
I am having some trouble interpolating and am finding very little information on DAX interpolation. I have tried to do it myself using the Y = Y1 + (Y2 - Y1)/(X2 - X1) * (X - X1) formula, but it doesn't seem to be working for me. Would someone mind looking at my code to see how I can complete the very last column? (I have done this in an Excel file as Power BI has a limit on the rows you can enter into Power Query manually, which would mean two separate files).
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.
11 Replies
- AlB
Community Champion
Hi justlogmein
You can use the solutions to the question you posted earlier to get the previous and next Y values and X values and calculate the interpolated value with the formula you show
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
justlogmeinYes, you'll see I have already gotten the previous and next values needed for the formula, but it is not giving the correct results.
- AlB
Can you share the pbix where youa re implementing this? I do not see any formula in the Excel you shared and in any case we'd still ave to look at the DAX code to see what is wrong
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
- justlogmein
In the Excel file, go to Data > Load Data Model
- tamerj1
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
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
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.
