Calculate new value based on previous on same colum

[Giancarlo1977]

Hi,

I am running a project and what I am trying to do is to calculate a Planned Repair based on StringW and UR (Under Repair)

StringW = Demand which is different every week

AV = Current Total Capability (coming from system)

UR = Current Under Repair (coming from system)

RTG = Ready to Go : AV-UR

StringWx = Is my input which is the weekly demand

AV = Is a fix number and it is my current total capability

UR_x = After the starting number and based on Planned Repair then this number will be recalculated and change over week

RTG_x = After the starting number and based on Planned Repair this number will be recalculated and change over week

I am doing this in excel by creating several column for each week like the example below

ToolCode	String W1	String W2	String W3	String W4	AV	UR_1	RTG_1	Planned Repair_1	UR_2	RTG_2	Planned Repair_2	UR_3	RTG_3	Planned Repair_3	UR_4	RTG_4	Planned Repair_4
aaa	40	38	42	44	41	3	38	2	1	40	0	1	40	1	0	41	0

The formula that I am using in excel to calculate the Planned Repair_1 is:

=IF(UR_1=0,0,IF(RTG_1>=StringW1,0,IF(StringW1>(UR_1+RTG_1),UR_1,MIN(UR_1,ABS(RTG_1-StringW1)))))

For the following week I have recalculated the UR_2 = UR_1-Planner Repair_1 and RTG_2 = AV-UR_1.

Therefore the new Planned Repair_2 will use the same formual but it will consider UR_2 and RTG_2

Planner Repair_2 = =IF(UR_2=0,0,IF(RTG_2>=StringW2,0,IF(StringW2>(UR_2+RTG_2),UR_2,MIN(UR_2,ABS(RTG_2-StringW2)))))

And like this for all weeks in different column (which is difficult to see)

I am not good in excel so that is the only way I found 

I would like to have same results in 1 column in Power BI since my project is automated in PowerBI so if I can figure out the correct way it will by automatically refreshed giving me everytime the new plan and it is easier to monitor

So I am trying to find a way to calculate the following columns:

Planned Repair

New UR

New RTG

ToolCode	WeekName	String per week	AV	UR_1	RTG_1	Planned Repair (Calculated Column)	New UR(Calculated Column)	New RTG(Calculated Column)
aaaa	1	40	41	3	38	2	3	38
aaaa	2	38	41	3	38	0	1	40
aaaa	3	42	41	3	38	1	1	40
aaaa	4	44	41	3	38	0	0	41

Really appreciate all your support

Thanks

[v-kongfanf-msft]

Hi @Giancarlo1977 ,

You can try below formula to created Planned Repair, New UR, New RTG calculated column:

Planned Repair = 
VAR CurrentWeek = 'Table'[WeekName]
VAR UR = 'Table'[UR_1]
VAR RTG = 'Table'[RTG_1]
VAR StringW = 'Table'[String per week]
RETURN
IF(
    UR = 0, 0,
    IF(
        RTG >= StringW, 0,
        IF(
            StringW > (UR + RTG), UR,
            MIN(UR, ABS(RTG - StringW))
        )
    )
)

New UR = 
VAR CurrentWeek = 'Table'[WeekName]
VAR PrevWeek = CurrentWeek - 1
VAR PrevUR = CALCULATE(
    MAX('Table'[UR_1]),
    FILTER('Table', 'Table'[WeekName] = PrevWeek)
)
VAR PrevPlannedRepair = CALCULATE(
    MAX('Table'[Planned Repair]),
    FILTER('Table', 'Table'[WeekName] = PrevWeek)
)
RETURN
PrevUR - PrevPlannedRepair

New RTG = 
'Table'[AV] - 'Table'[New UR]

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Giancarlo1977]

Hi,

Thanks for your reply but unfortunately the formula is not working for me.

When I run it I have the following value:

Week	String per week	AV	UR	RTG	Planned Repair	New UR
1	42	40	5	35	5
2	30	40	5	35	0	0
3	24	40	5	35	0	5
4	18	40	5	35	0	5
5	44	40	5	35	5	5
6	92	40	5	35	5	0

The value hilighted in bolt are not correct (based on what I am looking for)

The Planned Repair in week 5 and week 6 should be 0 because the New UR from Week 2 till Week 6 should always be 0 since I have repaired all 5 in week 1.

So since in week 1 my Planned Repair is 5 my new UR will be 0 and RTG will be 40. So the new Planned Repair formula for the following weeks should consider what happened in previous weeks

I hope this is clear and I appreciate your help