Forum Discussion
Helper IISum interval considering many filters
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!
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.
6 Replies
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.
ClaudioFwonderfull!,
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;
- ClaudioF
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.
- bhanu_gautam
Super User
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)- ClaudioF
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;
- ClaudioF
this is the link to the dashboard, it didnt work using the code you provided;
