interpolation

2 Topics

Interpolation on multiple subset of a table
Hello everyone, I’m working on a project where I need to manage a planning on a power bi. My planning (a big table) is composed of Task and sub-task. I’m only working at the Task level to manage my planning, but each modification will also change each sub-task of the modified task. Each sub-task has a quantity per year. The total quantity and the quantity distribution are fixed, whatever the task modification. So, if the duration is identical before and after the modification, we will only shift the quantity. If the duration is different before and after the modification, we will have a weighted interpolation to conserve the quantity and the distribution. I already found how to interpolate with power bi in the Microsoft community : https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712 but it’s at the table level, not subset of the table. I create an example of my situation with quantity before and after the modification at the task level: Task Sub Task Date Interpolating factor_Old Interpolating factor New Quantity_Old Quantity_New T1 ST1 2000 1 0 50 0 T1 ST1 2001 2 0 100 0 T1 ST1 2002 3 1 150 50 T1 ST1 2003 0 2 0 100 T1 ST1 2004 0 3 0 150 T1 ST1 2005 0 0 0 0 T1 ST2 2000 0 0 0 0 T1 ST2 2001 1 0 75 0 T1 ST2 2002 2 1 75 75 T1 ST2 2003 0 2 0 75 T1 ST2 2004 0 0 0 0 T1 ST2 2005 0 0 0 0 T1 ST3 2000 0 0 0 0 T1 ST3 2001 0 0 0 0 T1 ST3 2002 0 0 100 0 T1 ST3 2003 0 0 0 0 T1 ST3 2004 1 1 0 100 T1 ST3 2005 0 0 0 0 T2 ST1 2000 1 0 25 0 T2 ST1 2001 4 0 25 0 T2 ST1 2002 0 1 0 12,5 T2 ST1 2003 0 2 0 12,5 T2 ST1 2004 0 3 0 12,5 T2 ST1 2005 0 4 0 12,5 T2 ST2 2000 1 0 50 0 T2 ST2 2001 4 0 100 0 T2 ST2 2002 0 1 0 25 T2 ST2 2003 0 2 0 33 T2 ST2 2004 0 3 0 42 T2 ST2 2005 0 4 0 50 T2 ST3 2000 1 0 200 0 T2 ST3 2001 4 0 300 0 T2 ST3 2002 0 1 0 100 T2 ST3 2003 0 2 0 117 T2 ST3 2004 0 3 0 133 T2 ST3 2005 0 4 0 150 A first table summarize the initial situation: Task from to Duration Shift Interpolation T1 2000 2002 3 0 no T2 2000 2001 2 0 no And a second table summarize all modifications (T1 is only shifted, T2 is shifted and interpolated): Task from to Duration Shift Interpolation T1 2002 2004 3 +2 no T2 2000 2001 2 0 no So, on my primary table I need to: create “interpolating factor old” and “interpolating factor new” columns create “Quantity new” column based on modifications. My problem is that I have an idea on how to do each part of my workflow, but I don’t know how to apply it for each sub-task and not for all my table. The only solution that I found is to have a formula for each sub-task with a fixed filter (ex : Table[Sub task] = “ST1”). But in my case, I have more than 30 000 sub-tasks. So, this solution is not possible. I have also tried a filter where: FILTER(Table, Table[Sub task] = EARLIER(Table[Sub task])) but it doesn’t worked. I have already search in the community about my problem, but I didn’t find a similar situation. But maybe I’m not using the correct wording. And in fact, maybe I’m not using the more adequate method to do it. So, I need your help to know how to apply formulas for each sub-task without a filter for each sub-task. If you need more information, I can provide them. Thanks,
Solved
CGU
1 year ago Place DAX Commands and Tips
522Views
0likes
2Comments
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 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). DAX Interpolation Problem
Solved
justlogmein
4 years ago Place DAX Commands and Tips
5.1KViews
0likes
11Comments