topic Re: DAX Measure Cumulative subtraction Total that is similar to Excel =IF(C2>B3,0,B3-C2) in DAX Commands and Tips

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

kLizzy — Tue, 22 Dec 2020 18:19:57 GMT

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

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

lbendlin — Tue, 22 Dec 2020 23:25:06 GMT

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.

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

kLizzy — Wed, 23 Dec 2020 02:08:14 GMT

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)

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

mahoneypat — Thu, 24 Dec 2020 13:38:35 GMT

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

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

kLizzy — Sun, 27 Dec 2020 07:17:20 GMT

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!

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

littlemojopuppy — Sun, 27 Dec 2020 18:16:19 GMT

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.

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

kLizzy — Mon, 28 Dec 2020 15:23:15 GMT

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

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

littlemojopuppy — Mon, 28 Dec 2020 15:29:25 GMT

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

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

kLizzy — Mon, 28 Dec 2020 17:08:20 GMT

yes

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

littlemojopuppy — Wed, 30 Dec 2020 15:53:14 GMT

Download from here and take a look...

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

kLizzy — Thu, 07 Jan 2021 14:09:07 GMT

Thank you for getting me to the right direction!

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

littlemojopuppy — Thu, 07 Jan 2021 14:10:24 GMT

You're welcome! Glad I could help! 🙂