Forum Discussion
Cumulative formula inside IF formula
Hi Team,
I am looking for a formula that can help me in planning my repair. I have an if function but eventually I need to reapply the calculation based on the new value coming after the previous calculation.
The below screen can explain better what I am looking for
This is my main formula. But the value of UR should be re-evaluate based on how many I repaired the previous week
My Result should be in the Cumulative Under Repair for same Concatenate Tool Code (which is wrong now!)
For 19 May = 2 (just the qty I neew based on String per week)
26 May = 0 (no demand so no need to repair)
2 Jun = 1 (because I have already repaired 2 19 May so my new count of UR is 1 and RTG is 40
9 Jun = 0 (because I have repaired all UR already)
21 July = 0 (because I have repaired all UR already)
I am struggling how to include this cumulative in the formula.
Anybody can help?
Appreciate all your support
Regards
5 Replies
you may not need to do this cummulative.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523lbendlin ,
Thanks for your reply.
I have attached a table with what I am looking for. Planner Repair Column is what I am looking to calculate
ToolCode WeekName String per week AV UR RTG Planned Repair aaaa 1 40 41 3 38 2 aaaa 2 38 41 3 38 0 aaaa 3 42 41 3 38 1 aaaa 4 44 41 3 38 0 bbbb 1 15 20 6 14 1 bbbb 2 16 20 6 14 1 bbbb 3 17 20 6 14 1 bbbb 4 19 20 6 14 2 Hi,
anybody can help me here?
I am lost and it is difficult also to explain in formula what I am trying to achieve.
At the moment I did in excel by adding different column everytime as per example below where:
At the end RTG should be =< String number
ToolCode String W1 String W2 String W3 String W4 Total Available (UR+RTG) 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 Where
UR_1 = UR - Planned Repair_1
UR_2 = UR_1 - Planned Repair_2 etc etc
In other words I balance the UR to repair the one that I need to match WeekName
Now since I have all project in PowerBI and I have to plan for 12 weeks I was trying to put in my Dashboard in a column so that it will be easier to monitor and the dashboard should look like this. Total Available, UR and RTG will not change. Planned Repair will be recalculated considering the new count of UR - Previous Planned Repair
ToolCode WeekName String per week Total Available UR RTG Planned Repair New UR New RTG 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 I hope this will make more sense. I have no idea how to achieve this. I was trying with cumulative, Measure and previous Raw data but still no success
Thanks a lot for your help
