DAX Measure Cumulative subtraction Total that is similar to Excel =IF(C2>B3,0,B3-C2)

[kLizzy]

Could anyone help to get the measure to work like =IF(C2>B3,0,B3-C2) in Excel.

Invetory row is the data from the table, Step 1 and Step2 rows are the measures need to create in DAX

for example Cell C3 =IF(C2>B3,0,B3-C2)

                                = IF(50>150, 0, 150-50) 

                                = 100

Cell D3 =IF(D2>C3,0,C3-D2)

                                = IF(40>100, 0, 100-40) 

                                = 60

and so on..

Step 2 has similar calculation. If you could help with Step 1 measure I would greatly appreciate it.

		Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20
Inventory		50	40	0	0	0	0	20	30	50
Calc Logic Step 1: Cumulative total	150	100	60	60	60	60	60	40	10	0
Step 2:Final Cal	0	1	2	3	4	5	6	7	8	8.2

[mahoneypat]

If you can share your pbix (link to it on OneDrive, Google Drive, etc.) or a mock up with a represenatitive model, a specific expression can be suggested.  Basically, you need to create two variables to calculate the values you'll compare, and then use those in an IF to do the comparison in the Return part of the measure.

Regards,

Pat

[kLizzy]

I have pbi file at the location below.

https://drive.google.com/file/d/1rDFWhJ0yvlQKr4CcVSNm3VhODHQHnr_E/view?usp=sharing

I am trying to calculate "Supply"

There are 2 step to calculate this line

Step 1: Cumulative Netting : formula is IF(Demand Total > Prior Cumulative Netting, 0, Prior Cumulative Netting-Cumulative netting)

           From data. 2020-12 = IF(3145>3770, 0, 3770-3145) = 625

                             2021-01 = IF(11362>625,0,625-11362) = 0

Step 2. Supply formula is IF (Step1 = 0, Prior Cumulative Netting/Demand Total, Prior Supply + 1)

 From data. 2020-12              = IF (625 = 0, 3770/3145,0+1)   = 1

                     2021-01           =  IF(0=0,625/11362,1+1) = 0.05

I greatly appreciate your help!

[littlemojopuppy]

@kLizzy would like to ask a favor...can you please explain in Plain English what the calculation for Supply should be WITHOUT referring to an Excel formula?  Need to get some context of what you're trying to calculate without deciphering formulas.  I believe you have inconsistencies in the formulas that is making this very hard to figure out what you're after.  Or if you can provide a link to some background information on supply/demand forecasting, inventory management or other subject matter that would be very helpful.

[kLizzy]

the Supply that I am trying to get to is actually a Months Supply (number of months for the supply). 

1) calculate the cumulative netting each month, if demand > on hand then 0, else On hand - Demand 

2) Months supply, if cumulative netting = 0 then Prior month cumulative netting/Demand, else Prior Months supply +1

[littlemojopuppy]

Fair to say that "cumulative netting" is another way to say "inventory remaining after satisfying demand" in any given month?

[kLizzy]

yes

[littlemojopuppy · Accepted solution]

Download from here and take a look...

[kLizzy]

Thank you for getting me to the right direction!

[littlemojopuppy]

You're welcome!  Glad I could help!  🙂

[lbendlin]

Does it have to be a DAX measure? Is the result impacted by user interaction with filters?

If yes then you can apply one of the "find previous row" patterns. Be aware that this is not trivial, and at the very least you need a sortable master column (the date column in your example). It is also assumed that you have unpivoted the data in step 2 into a more usable format.

[kLizzy]

@lbendlin yes it has to be DAX measures. and yes the result impacted by user interaction with filters.  the column is sorted by date column. The cell formula on step 2 is similar to step 1. It is using the result from step 2 in the formula =IF(C3=0,B3/C2,B4+1)