Interpolation on multiple subset of a table

[CGU]

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,

[bhanu_gautam · Accepted solution]

 Create two new columns in your table for the old and new interpolating factors. You can use the following DAX formulas:

DAX
InterpolatingFactor_Old =
VAR TaskStartDate = CALCULATE(MIN(Table[Date]), ALLEXCEPT(Table, Table[Task]))
VAR TaskEndDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Task]))
RETURN
DIVIDE(Table[Date] - TaskStartDate, TaskEndDate - TaskStartDate, 0)

DAX
InterpolatingFactor_New =
VAR TaskStartDate_New = CALCULATE(MIN(Table[Date]), ALLEXCEPT(Table, Table[Task])) + [Shift]
VAR TaskEndDate_New = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Task])) + [Shift]
RETURN
DIVIDE(Table[Date] - TaskStartDate_New, TaskEndDate_New - TaskStartDate_New, 0)

Create a new column for the new quantities based on the interpolating factors:

DAX
Quantity_New =
VAR TotalQuantity = CALCULATE(SUM(Table[Quantity_Old]), ALLEXCEPT(Table, Table[Task], Table[Sub Task]))
VAR OldFactor = Table[InterpolatingFactor_Old]
VAR NewFactor = Table[InterpolatingFactor_New]
RETURN
IF(
OldFactor = 0,
0,
TotalQuantity * NewFactor / OldFactor
)

Ensure that the formulas are applied dynamically for each sub-task without hardcoding filters. The use of ALLEXCEPT in the DAX formulas ensures that the calculations are performed within the context of each task and sub-task.

@CGU

[CGU]

Thank you for your quick answer.

At the beginning I used all your answer but after some test I need to modify it to adapt my project organisation.

But it's worked and it's very nice.

Thanks