topic Re: Deduct values until it turns 0 then use 0 in DAX Commands and Tips

Deduct values until it turns 0 then use 0

Powerwoman — Thu, 01 Feb 2024 14:23:39 GMT

Dear friends,
I am working on a measure called 'remainingQuanity' that deducts the [Quantity] from [Inventory Units].

Sample table:

Index	remainingQuantity	inventoryUnits	Quantity
82078	3	4	1
82079	4	4	-1
82080	3	4	1
82081	2	4	1
82082	1	4	1
82083	0	4	1
82084	-1	4	1
82085	-2	4	1
82086	-3	4	1
82087	-4	4	1

Once the [remainingUnits] becomes 0 it should use 0 instead of the calculation:

It's sound so easy, but what's the right dax?

Thank you

Re: Deduct values until it turns 0 then use 0

bhanu_gautam — Thu, 01 Feb 2024 15:33:40 GMT

Powerwoman , Try using below method

RemainingQuantity =
CALCULATE(
SUMX(
FILTER(
ALL('YourTable'),
'YourTable'[Index] <= MAX('YourTable'[Index])
),
'YourTable'[Quantity]
),
FILTER(
ALL('YourTable'),
'YourTable'[Index] <= MAX('YourTable'[Index])
)
)

Please accept as solution and give kudos if it helps

Re: Deduct values until it turns 0 then use 0

ThxAlot — Thu, 01 Feb 2024 15:35:22 GMT

Re: Deduct values until it turns 0 then use 0

talespin — Thu, 01 Feb 2024 16:19:44 GMT

Hi Powerwoman

Another method, as calculated column

Column =

VAR _Index = TestTbl1[Index]

VAR _inventoryUnits = TestTbl1[inventoryUnits]

VAR _SumQty = CALCULATE(SUM(TestTbl1[Quantity]), REMOVEFILTERS(TestTbl1), TestTbl1[Index] <= _Index)

VAR _result = _inventoryUnits - _SumQty

RETURN IF( _result > 0, _result, 0)

Re: Deduct values until it turns 0 then use 0

Powerwoman — Thu, 01 Feb 2024 16:25:08 GMT

ok, update:
I need this calculation to be done by item no.
Is that possible?

Index	Item_No	inventoryUnits	Quantity
82078	048454	4	1
82079	048454	4	-1
82080	048454	4	1
82081	048454	4	1
82082	048454	4	1
82083	048454	4	1
82084	048454	4	1
82085	048454	4	1
82086	048454	4	1
82087	048454	4	1
82088	999999	4	1
82089	999999	4	-1
82090	999999	4	1
82091	999999	4	1
82092	999999	4	1
82093	999999	4	1
82094	999999	4	1
82095	999999	4	1
82096	999999	4	1
82097	999999	4	1

Re: Deduct values until it turns 0 then use 0

Powerwoman — Thu, 01 Feb 2024 16:26:27 GMT

Hi ThxAlot ,
it seems like this is working.
Can it be done by item no as well?

Index	Item_No	inventoryUnits	Quantity
82078	048454	4	1
82079	048454	4	-1
82080	048454	4	1
82081	048454	4	1
82082	048454	4	1
82083	048454	4	1
82084	048454	4	1
82085	048454	4	1
82086	048454	4	1
82087	048454	4	1
82088	999999	4	1
82089	999999	4	-1
82090	999999	4	1
82091	999999	4	1
82092	999999	4	1
82093	999999	4	1
82094	999999	4	1
82095	999999	4	1
82096	999999	4	1
82097	999999	4	1

Re: Deduct values until it turns 0 then use 0

Powerwoman — Thu, 01 Feb 2024 16:29:25 GMT

Hi talespin,
this works fine, thank you.

Can it be done by item no as well?

Index	Item_No	inventoryUnits	Quantity
82078	048454	4	1
82079	048454	4	-1
82080	048454	4	1
82081	048454	4	1
82082	048454	4	1
82083	048454	4	1
82084	048454	4	1
82085	048454	4	1
82086	048454	4	1
82087	048454	4	1
82088	999999	4	1
82089	999999	4	-1
82090	999999	4	1
82091	999999	4	1
82092	999999	4	1
82093	999999	4	1
82094	999999	4	1
82095	999999	4	1
82096	999999	4	1
82097	999999	4	1

Re: Deduct values until it turns 0 then use 0

talespin — Thu, 01 Feb 2024 17:22:49 GMT

Hi Powerwoman ,

Yes, But I need something to order, which attribute?

Re: Deduct values until it turns 0 then use 0

Powerwoman — Thu, 01 Feb 2024 18:05:55 GMT

Hi talespin ,
great! That would be item no & index.

The table is already sorted this way.
Can it be grouped by item no and sorted by index?

Re: Deduct values until it turns 0 then use 0

talespin — Thu, 01 Feb 2024 18:16:40 GMT

Hi Powerwoman ,

Column =

VAR _Index = TestTbl1[Index]

VAR _inventoryUnits = TestTbl1[inventoryUnits]

VAR _ItemNo = TestTbl1[Item_No]

VAR _SumQty = CALCULATE(SUM(TestTbl1[Quantity]), REMOVEFILTERS(TestTbl1), TestTbl1[Index] <= _Index, TestTbl1[Item_No] = _ItemNo)

VAR _result = _inventoryUnits - _SumQty

RETURN IF( _result > 0, _result, 0)

Re: Deduct values until it turns 0 then use 0

Powerwoman — Fri, 02 Feb 2024 08:41:11 GMT

Hi talespin
Thanks for your solution.
The only 'bad' thing was that it killed my Power BI desktop because the original table had 90.000 rows.

My solution to this:

Different calculation of the 'running Total'

var _runningTotalQuantity =
sumx(
FILTER(
all(table),
table[Item_No] = EARLIER(table[Item_No]) &&
table[Index] <= EARLIER(table[Index])
),
table[Quantity]
)

Re: Deduct values until it turns 0 then use 0

talespin — Fri, 02 Feb 2024 08:46:32 GMT

hi Powerwoman

90,000 shouldn't be a problem, if possible please share pbix file, removing any sensitive data.