Forum Discussion
Calculate new value based on previous on same colum
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
2 Replies
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 - PrevPlannedRepairNew RTG = 'Table'[AV] - 'Table'[New UR]Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
