Projected "Rolling Iventory" Stock Matrix

[snimphie]

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:

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached files.

[Manoli21]

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 !

[Ashish_Mathur]

You are welcome.  I cannot understand what you want.  Show the expected result clearly.

[Manoli21]

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

[Ashish_Mathur]

Share the download link of the PBI file.

[Manoli21]

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 :

Running E till previous month = CALCULATE([Expenses],DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),min('Calendar'[Date])-1))

Running R till previous month = CALCULATE([Revenue],DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date

I have to limitate datas in order to not catch up datas from the month before the actual month for E and R, is it possible ? Because my "initial backlog" is already updated with expenses and revenues of the previous month.

In your exemple 1, it's works perfectly because you do not have E and R before december.

Thank you

[Manoli21]

In these formulas I would like only the expenses/revenues of the last month but not cumulate all the month before

Thanks!

[snimphie]

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

[Ashish_Mathur]

You are welcome.