Forum Discussion
Helper IIIMatrix visual with stock balance per week
Hi.
Based on above tables I would like to create a matrix much like the shown. Besides from a Date table I have the 3 above tables. The Stock table has one row per articles and the 2 other tables have multiple rows per article.
Is it possible to create such a matrix? So far I have made a matrix with inbound and outbound values, but I am not sure how to add the stock values.
Thanks a lot.
Helen
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/
6 Replies
- amitchandak
Super User
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/
HEWIt works great - thanks!
I would like the date range to be limited to the weeks with transactions. For example, we are not interested in week 45 2020 if the last inbound/outbound transaction is in week 25 2020. Can I somehow with min/max for both tables (inbound and outbound) limit the date range?
Br.
Helen
- HEW
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
- amitchandak
Can you share some sample data to test. make me @
Appreciate your Kudos.