Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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).
Solved! Go to Solution.
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.
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 )
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?
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.
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.
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.
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
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. |
In the Excel file, go to Data > Load Data Model
Yes, you'll see I have already gotten the previous and next values needed for the formula, but it is not giving the correct results.
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. |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
14 | |
12 | |
10 | |
10 |