Forum Discussion
Helper IIIMatrix visual with stock balance per week
With a date dimension, you should able to do so. Matrix Format I doubt
Final Stock= CALCULATE(SUM(Sales[stock ]) + Cumm Sales = CALCULATE(SUM('Inbound'[Qty]),filter(date,date[date] <=maxx(date,date[date]))) - Cumm Sales = CALCULATE(SUM('Outbound'[Qty]),filter(date,date[date] <=maxx(date,date[date])))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Helper III
I seem to have a problem. If you look at below matrix the stock qty is 111 pcs, which is correct. However, we have a sales of 120 pcs. in week 12, so we are lacking 9 pcs. The matrix goes back to 111 pcs. in week 13 even though it should still be -9 pcs. as we don't have any inbound orders.
My formula is:
Final Stock:= CALCULATE(SUM(Sales[Stock]) + CALCULATE(SUM('Inbound[Qty]);filter('Date';'Date'[Date] <=maxx('Date';'Date'[Date]))) - CALCULATE(SUM('Outbound'[Qty]);filter('Date';'Date'[Date] <=maxx('Date';'Date'[Date]))))
Thanks a lot in advance.
Helen
I'm interested to hear how you solved this ? This seems to be an outstanding item in this thread. Cheers, Wdr14