topic Dax to count Available Stock based on Demand in DAX Commands and Tips

Dax to count Available Stock based on Demand

H_insight — Mon, 09 May 2022 12:09:54 GMT

Hi,

I am trying to have a DAX measure to:

1- calculate the total stock at the beginning of selected date where stock status = Delivered per region.

2- calculate total demand per selected date

Stock table:

Demand Table:

Sample file

Many thanks

Re: Dax to count Available Stock based on Demand

Whitewater100 — Mon, 09 May 2022 13:13:10 GMT

Hi:

I added some dimension tables. I hope this is what you are looking for!

https://drive.google.com/file/d/1Rx0tlNTWIi6xzTtYEPhUdYTY_jDQ-k6G/view?usp=sharing

Re: Dax to count Available Stock based on Demand

H_insight — Mon, 09 May 2022 16:37:45 GMT

Hi Whitewater100 ,

Thanks for the quick response. Further to your solution, how can I view the remaining product balance in stock after that date?

For example, in the date range of 10/06/22 to 23/06/22 I had only 40 products (Status = Delivered):

and the demand was 94 . So I am short by 54 products based on that date range for that specific region. So far so good, but I trying to get the balance beyond 23/60/22? Let's say data range from 24/6/22 to 30/6/22 with a new total demand of 47, that will increase the gap to (54 +47) =101, unless new products were Delivered. Not sure if it's possible or not.

Updated file link

Many thanks for your help.

Re: Dax to count Available Stock based on Demand

Whitewater100 — Mon, 09 May 2022 19:23:23 GMT

Hi:

I think you want to match on your product ID's? What do the ID's represent? There is many id's and multiple regions for Product 1. It looks like you may be comparing deliveries for 9&10 vs Demand for different products?

To answer the questions you can try doing a ytd measure for both. Please see attached link.

https://drive.google.com/file/d/1Rx0tlNTWIi6xzTtYEPhUdYTY_jDQ-k6G/view?usp=sharing

You can add a YTD calculation if this is only one product under discussion.

YTD Stock(Delivered) = TOTALYTD([Stock(Delivered)],'Date'[Date])

I hope the two questions are answered now...

1- calculate the total stock at the beginning of selected date where stock status = Delivered per region.

2- calculate total demand per selected date

Re: Dax to count Available Stock based on Demand

H_insight — Mon, 09 May 2022 19:29:56 GMT

Thanks for heping with the extra mile 🙂

Re: Dax to count Available Stock based on Demand

Whitewater100 — Mon, 09 May 2022 19:38:01 GMT

You are welcome. Have a nice week ahead..