Dax to count Available Stock based on Demand

[H_insight]

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

[Whitewater100 · Accepted solution]

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 

[H_insight]

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.

[Whitewater100]

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

[H_insight]

Thanks for heping with the extra mile 🙂

[Whitewater100]

You are welcome. Have a nice week ahead..