Need help with measures - Demand and Usage

[NewBIe2013]

BI gurus, I'm a beginner on DAX/Measures.

I have this Excel and the calculation rather easy to achieve, and would like to convert it to Power BI, did some research from online and this community, went through a couple of resolutions from online such as cross-highlight, current month, and future month, and couldn't get my head around.

Hence would really need your help on the Planned and Future Qty, they are from same table called Forecast with Order date and Delivery date. 

This probably needs a couple of measures, and want to achieve:

- Planned Qty: qty order on the month but may be deliver in the following months;

- Future Qty: qty might need to order next month and deliver in the following months;

- Stock level:

  > capture the current stock level (i.e. 127), then from next month onwards, it would be [(current stock level + Planned qty + Future qty) - Usage]

Thanks.

[NewBIe2013]

Hi @v-tianyich-msft , thanks for that. 
need some clarifications on the measure, you have put in the var _num = 127, can it be update to take from the current on hand stock level (even though it still 127)? Reason to ask for change is because when comes to Apr, the current on hand will be different. Look forward to your response. Thanks. 

[NewBIe2013]

Future Qty = sum of dark pink colour cells. 

[v-tianyich-msft · Accepted solution]

Hi @NewBIe2013 ,

Since I don't know the source of the first stock level, I used the formulated data to complete your idea:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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