topic Re: Calculate sum of items based on date range and avaliable stock in DAX Commands and Tips

Calculate sum of items based on date range and avaliable stock

ClaudioF — Sun, 12 Jan 2025 16:40:30 GMT

Hey everyone, hope you all well.

I'm new in the power bi, and having lots of trouble allready, can you help me with a situation?

I need to set a status to the items of different orders based on its date and comparation between the stock and the quantity needed.

Example: i have three tables called: date, with comun: date; orders, with columns: order number, item, quantity and date; and stock that has columns: item, quantity.

they have for example the following data:

Table date:

date

01/01/25

02/01/25

03/01/25

Table stock:

item	quantity
aaa	20
bbb	10

Table orders:

Order num	Item	quantity	date
111	aaa	20	01/01/25
111	bbb	10	01/01/25
222	aaa	10	02/01/25
333	aaa	20	03/01/25
333	bbb	2	03/01/25

i need a way of creating a graphic table to show, as i select a range of date in data segmentation for example: i select a range from 02 to 03/01, then based on the available stock, the status of the order number 222 will be: item aaa"available", and the other 333 item aaa will be "out" and the order 333 item bbb will be "available" but if i do not select any range and leave it all the dates selected, it will be: order 111, items aaa and bbb will be atatus "available" and the others will be "out".

I also need a way to count all the "available" and "out"

and i need to be able to select if i wanna se only the evailable or the out status.

i don't know if i made myself clear, but could you help me please?

Thankyou a lot.

Re: Calculate sum of items based on date range and avaliable stock

DataNinja777 — Sun, 12 Jan 2025 17:18:09 GMT

Hi ClaudioF ,

To dynamically assess the status of order items based on stock availability and selected date ranges in Power BI, you can follow these steps:

First, ensure your data model includes three tables: 'Orders' with columns for Order Number, Item, Quantity, and Date; 'Stock' with columns for Item and Quantity; and a separate 'Date' table to facilitate date filtering. Establish relationships between these tables: link the 'Orders' table to the 'Date' table via the Date column, and connect the 'Stock' table to the 'Orders' table through the Item column.

Next, calculate the cumulative stock available up to a selected date. Create a measure named 'StockOnHand' using DAX:

StockOnHand = VAR SelectedDate = MAX('Date'[Date]) RETURN CALCULATE( SUM('Stock'[Quantity]) - SUMX( FILTER('Orders', 'Orders'[Date] <= SelectedDate), 'Orders'[Quantity] ) )

This measure computes the stock on hand for each item up to the selected date.

Then, determine the status of each item by adding a calculated column in the 'Orders' table. Use the following DAX formula:

ItemStatus = VAR StockAvailable = [StockOnHand] RETURN IF('Orders'[Quantity] <= StockAvailable, "Available", "Out")

This column compares the order quantity with the available stock and assigns the status accordingly.

To enable date range filtering, add a Date Slicer to your report. Connect this slicer to the 'Date' table, allowing dynamic filtering of orders based on the selected date range.

For visualization, create a table or matrix to display the Order Number, Item, Quantity, Date, and Item Status. Ensure the Date Slicer affects this visual to reflect the selected date range.

To count the number of items with each status, create two measures:

CountAvailable = CALCULATE( COUNTROWS('Orders'), 'Orders'[ItemStatus] = "Available" ) CountOut = CALCULATE( COUNTROWS('Orders'), 'Orders'[ItemStatus] = "Out" )

These measures tally the items marked as "Available" and "Out," respectively.

Finally, to filter the visual by item status, add a slicer. Create a new table with distinct statuses ("Available" and "Out"), establish a relationship between this table and the 'Orders' table based on the ItemStatus column, and use this field in the slicer. This setup allows users to filter the displayed orders based on their status.

By implementing these steps, you can create a dynamic report in Power BI that displays the status of order items based on stock availability and selected date ranges, facilitating interactive filtering and providing insights into inventory management over time.

Best regards,

Re: Calculate sum of items based on date range and avaliable stock

ClaudioF — Sun, 12 Jan 2025 23:47:06 GMT

The resoult was not what i wanted, the first marked yellow should be available, due to the dates being the earlierst. the others should be out, but in this range of date, thtas not right. what sould i do?

Re: Calculate sum of items based on date range and avaliable stock

Anonymous — Wed, 15 Jan 2025 07:33:51 GMT

Hi ClaudioF ,

I want to acknowledge valuable input provided by DataNinja777 . Their initial ideas help guide my approach. However, I noticed that more details are needed to fully understand this issue.

(1) My test data is the same as yours. This is my model relationship.

(2) We can create a slicer table.

StatusSlicer = DATATABLE ( "status", STRING, { { "available" }, { "out" } } )

(3) We can create measures.

ItemStatus = var _quantity=CALCULATE(SUM('Table orders'[quantity]),FILTER(ALLSELECTED('Table orders'),[Item]=MAX('Table orders'[Item]) && [date]<=MAX('Table orders'[date]))) 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)

Flag = IF(ISFILTERED('StatusSlicer'[status])=FALSE() || [ItemStatus]=SELECTEDVALUE('StatusSlicer'[status]),1,0)

(4) Place [Flag=1] on the visual object filter.

Best Regards,

Neeko Tang

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

Re: Calculate sum of items based on date range and avaliable stock

ClaudioF — Wed, 15 Jan 2025 16:50:52 GMT

Amazing! thats what i was looking for!

I only have one issue: when i select the checkbox of "out", it does not work properly, showing the avaliable ones too. Have any idea of how to solve it?

Still thankyou a lot. Since i'm new in this subject, i will keep on studying power bi until i'm a pro, so i'll investigate step by step the solution you gave in order to understand what i am doing hehe.

what i miss in this dashboard is the count of orders that has at least one "out" status in it, do you have any idea of how doing it?

plus. do you have the explanation of why the table stock is not connected to the orders table?

Re: Calculate sum of items based on date range and avaliable stock

Anonymous — Thu, 16 Jan 2025 07:52:16 GMT

Hi ClaudioF ,

Thank you for your feedback. Below are some improvements:

(1) Create a slicer table.

StatusSlicer = DATATABLE ( "status", STRING, { { "available" }, { "out" } } )

(2) Create measures.

Measure = var _quantity=CALCULATE(SUM('Table orders'[quantity]),FILTER(ALLSELECTED('Table orders'),[Item]=MAX('Table orders'[Item]) && [date]<=MAX('Table orders'[date]))) 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)

Status = IF(ISFILTERED('StatusSlicer'[status])=FALSE(),[Measure],SELECTEDVALUE('StatusSlicer'[status]))

Flag = IF(ISFILTERED('StatusSlicer'[status])=FALSE() || [Measure]=SELECTEDVALUE('StatusSlicer'[status]),1,0)

CountNum(out) = CALCULATE(DISTINCTCOUNT('Table orders'[Order num]),FILTER('Table orders',[Measure]="out"))

(3)Then the result is as follows.

As far as model relationships go, you can connect as many as you want and it won't affect this desired outcome.

Best Regards,

Neeko Tang

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

Re: Calculate sum of items based on date range and avaliable stock

ClaudioF — Thu, 16 Jan 2025 10:49:53 GMT

Exactly what i need! Thankyou a lot.

I will implement this in my work and study these new information.

Re: Calculate sum of items based on date range and avaliable stock

ClaudioF — Thu, 16 Jan 2025 10:50:38 GMT

Thankyou a lot for your inputs my friend! it helped a lot.