Forum Discussion

Giancarlo1977's avatar
Giancarlo1977
Frequent Visitor
2 years ago
Solved

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...
"FILTER"
dax formula
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

are you trying to compare string versus AV (assuming that means availability?)  ?  Can you provide more context what kind of process this is?

Giancarlo1977's avatar
Giancarlo1977
Frequent Visitor
2 years ago

Hi,

 

What I am trying to do is to calcualte the Planner Repair based on StringW and UR (Under Repair)

 

StringW = Demand

AV = Total Capability

UR = How many I have Under Repair

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 = Is the starting number and based on Planned Repair this number will change over week

RTG = Is the starting number and based on Planned Repair this number will change over week.

 

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


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_1,MIN(UR_2,ABS(RTG_2-StringW2)))))

 

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

 

I hope it is more clear and apologize for the confusion. I am not good in excel so that is the only way I found 

 

I would like to have same things 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

 

ToolCodeWeekNameString per weekTotal AvailableURRTGPlanned Repair (Calculated Column)New UR(Calculated Column)New RTG(Calculated Column)
aaaa140413382338
aaaa238413380140
aaaa342413381140
aaaa444413380041

Really appreciate all your support

Thanks again