Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
My goal is to create a Matrix visual that uses "Date" as the column headers, "ProductID" as the row headers, and to have 3 values per row.
The first value is the sum of Quantity from the SALES table. The second value is the sum of Quantity from the WORK ORDER table.
The third value is the one that I am having trouble creating. I would like to create a "Projected" inventory that looks into future Sales Orders and future Work Orders and subtracts Sales Order Quantity, adds Work Order Quantity, and returns the projected stock for that day in the future. The "starting value" is pulled from a Stock table, that lists current available inventory of each product.
I have included a simplified table layout and the expected result created in Excel.
Thank you, and please let me know if any clarification is needed.
Tables:
Expected Sample Result:
Solved! Go to Solution.
Hi,
I have solved a similar problem in the attached files.
Hello Ashish
Your solution helped me so much !! thank you!
I have a quick question,
In my picture in attached, I want that my opening backlog start during the actual month. You can see that the opening backlog is calculated with datas of January, February, March...
How my initial backlog can be equal to the opening backlog for the current month ? I think that I have to limitate datas from "revenue till preivous month and depense till previous month but I am not familiar with min/max. I have really copy all your formulas but i have not understood all yet
I have take the formulas of your "example 1"
Thank you !
You are welcome. I cannot understand what you want. Show the expected result clearly.
Hello,
The expected result is to not consider datas before the current month.
I do not want to consider datas for January February March.
I would like that my opening backlog starts only in April.
In my picture, the opening backlog is calculated with datas of "revenues" end "expenses" of all the months before. How do not consider datas in yellow ? and start with an opening backlog of 19 171 978 in April (=to initial backlog)
Thank you
Share the download link of the PBI file.
Hello Ashish,
I am sorry but I cannot share the datas, and there is so much data, I cannot make a summary..
I think my issue is only on these two formulas :
I will need some file to work with.
In these formulas I would like only the expenses/revenues of the last month but not cumulate all the month before
Thanks!
This is a very helpful file! I was able to work through this to get a solution that worked for my data. The main thing was that I had to add a date value to my Stock table, so I used the minimum value of the Work Order and Sales Order dates.
Thanks,
snimphie
You are welcome.