Calculating a balance for the next 6 months.

[charlessutton]

I have a scenario that I am having a hard time explaining and hope that I can clearly word this. 

I have a requirement to calculate raw material balances for the existing and the next 5 months. The current month "OpenBalance" is pulled from a table without a date. It is just the current inventory balance. The subsequent months balances are created by subtracting forecast and consumption and adding purchase orders. I can calcualte everything I need using measures for each month e.g. Month 0, Month 1, Month 2, Month 3, Month 4, Month 5. However, I cannot seem to get these measures to associate with Dates. I am drawing a blank. 

In this example I am using a switch command to calculate the value of the CC_MonthValue however, it is not taking the selection into consideration. I have highlighted an example. The value for M_openbalfinal0 is the correct value but when I try to pull the value up using the swictch command I get a the total for every stock code not just the stock code I have selected via slicer. 

[v-xulin-mstf]

Hi @charlessutton

You can filter the stock code you have selected via slicer with ALLSELECTED function.

Could you provide your MEASURE?

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[amitchandak · Accepted solution]

@charlessutton , You need a measure like

sum(Table1[Opening Balance])
+ CALCULATE(SUM(Table[Purchase]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))
- CALCULATE(SUM(Table[sales]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))