topic Re: Sum interval considering many filters in DAX Commands and Tips

Sum interval considering many filters

ClaudioF — Wed, 05 Feb 2025 03:42:27 GMT

Hello everyone,

I have been here a time ago to solve a problem within this same topic, the thing is, the solution was complete but i found out that one other thing was needed.

I need the following dax code, not to consider all the order as "out", when the sum of items reach the total of stok available, but, i need to test others orders to see if the amount of the stok can be used still, axample: i have orders 333, 444 and 555 with the item bbb, with quantities 2, 30 and 5, the stok is 10 for this item, the actual code, tests the stok to the orders and the result is : available, ou, out; but i need the result to be: available, out, available.. of course, following the same logic of the earlier order num, only inside of the range date i select. When i select the date 01/03/25 to 01/03/25, the only orders that have in this date are the in the example..

Measure =
VAR _date = CALCULATE(MAX('Table date'[date]), ALLSELECTED('Table date'))
VAR _quantity =
CALCULATE(
SUM('Table orders'[quantity]),
FILTER(
ALLSELECTED('Table orders'),
[Item] = MAX('Table orders'[Item]) &&
[date] <= MAX('Table orders'[date]) &&
[Order num] <= MAX('Table orders'[Order num]) -- Considera o número do pedido
)
)
VAR _stock =
CALCULATE(
SUM('Table stock'[quantity]),
FILTER(
ALL('Table stock'),
[item] = MAX('Table orders'[Item])
)
)
VAR _result = IF(_quantity <= _stock, "available", "out")
RETURN
IF(ISBLANK(_quantity), BLANK(), _result)

Thankyou a lot allready!

Re: Sum interval considering many filters

bhanu_gautam — Wed, 05 Feb 2025 05:16:25 GMT

ClaudioF , Try using

dax
Measure =
VAR _date = CALCULATE(MAX('Table date'[date]), ALLSELECTED('Table date'))
VAR _item = MAX('Table orders'[Item])
VAR _orders =
FILTER(
ALLSELECTED('Table orders'),
[Item] = _item &&
[date] <= _date
)
VAR _stock =
CALCULATE(
SUM('Table stock'[quantity]),
FILTER(
ALL('Table stock'),
[item] = _item
)
)
VAR _runningTotal =
SUMX(
FILTER(
_orders,
[Order num] <= EARLIER('Table orders'[Order num])
),
[quantity]
)
VAR _result = IF(_runningTotal <= _stock, "available", "out")
RETURN
IF(ISBLANK(_runningTotal), BLANK(), _result)

Re: Sum interval considering many filters

ClaudioF — Wed, 05 Feb 2025 10:44:49 GMT

https://app.powerbi.com/reportEmbed?reportId=4934aedb-3f6c-42ce-8ebc-7376d4af98ca&autoAuth=true&ctid=dee74457-d751-4011-a5c4-44560cf8b415

this is the link to the dashboard, it didnt work using the code you provided;

Re: Sum interval considering many filters

ClaudioF — Wed, 05 Feb 2025 16:42:11 GMT

This is the result i get when select the range date 01/03/25 using the code i gave:

The needed result should be: available, available, out, available;

The stock of the item bbb is 10;

Re: Sum interval considering many filters

Anonymous — Fri, 07 Feb 2025 09:36:04 GMT

Hi ClaudioF , hello bhanu_gautam, thank you for your prompt reply!

To meet your requirments, we suggest you use the measure instead of the column(we could not use measure in slicer).

Create the DateRange first, then add the measure to filter the visual:

DateRange = VAR _maxdate= CALCULATE(MAX('Table date'[Date]),ALLSELECTED('Table date'[Date])) VAR _mindate=CALCULATE(MIN('Table date'[Date]),ALLSELECTED('Table date')) RETURN IF(MAX('Table orders'[Date])<=_maxdate&& MAX('Table orders'[Date])>=_mindate,1,0)

Additionally, use this measure to verify the stock status for each line item based on the order number:

ItemStatus = VAR CurrentItem = SELECTEDVALUE('Table orders'[Item]) VAR CurrentOrderNum = SELECTEDVALUE('Table orders'[Order num]) VAR StockAvailable = CALCULATE( SUM('Table stock'[Quantity]), FILTER('Table stock', 'Table stock'[Item] = CurrentItem) ) VAR RunningStock = SUMX( FILTER( ALLSELECTED('Table orders'), 'Table orders'[Order num] <= CurrentOrderNum && 'Table orders'[Quantity]<=StockAvailable &&'Table orders'[Item]=CurrentItem ), 'Table orders'[quantity] ) RETURN IF( RunningStock <= StockAvailable && MAX('Table orders'[Quantity])<=StockAvailable, "available", "out" )

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Sum interval considering many filters

ClaudioF — Fri, 07 Feb 2025 16:54:12 GMT

wonderfull!,

its working just the way i need, its a little complex, i thought i would be able just to incude more things later onde, but, if its not much, how could i add a other filter of date? i mean, this one will filter a new column in the orders table which is the column of the max date of the order, then , i have the orders created from a range of time to other, and then, the dates that these orders might be finished. this delivery date, will impact in the calculation of the orders just like the creation date, the thing is, the delivery date is the last date to be filtered;

How could it be done?

Thankyou a lot allready for your help;

Re: Sum interval considering many filters

ClaudioF — Sat, 08 Feb 2025 22:52:24 GMT

Its no longer an issue, i just gost the solution by adding a new table called "delivery table", i made the correct relation between this and the orders table, then i placed a new data segmentation visual on the dashboard and inserted the date of delivery. The result was just right!

Thankyou all!! a lot.