I am trying to create a weekly inventory forecast by subtracting weekly sales forecast from the opening inventory. I cannot create a column for this as the weekly sales forecast is dynamically calculated using a measure.
I have created a matrix with weeks in columns field and products in rows field. The problem is that the matrix visual is considering opening inventory of each week equal to the opening inventory of the first week. It should consider closing inventory of previous week as opening inventory of current week.
Your help will be highly useful.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
Proud to be a Datanaut!
I am trying to explain the problem here.
1. I have created a measure to calculate the weekly sales forecast. This is dynamic as I need to change the reference historical week which changes the forecast. The matrix visual for the sales forecast looks like:
|Product||Week 1||Week 2||Week 3|
2. I have an inventory table which gives me opening inventory for Week 1. Now I want to calculate closing inventory for future weeks as: Closing Inventory = Opening Inventory - Sales Forecast.
I am facing a problem of Opening Inventory not being referred to previous week's closing inventory in matrix visual. It always refers to opening inventory of week 1.
|Week 1||Week 2||Week 3|
|Product||Opening Inventory||Sales Forecast||Closing Inventory||Opening Inventory||Sales Forecast||Closing Inventory||Opening Inventory||Sales Forecast||Closing Inventory|
Could you pls advise how to take last weeks' closing inventory as current week's opening inventory?
Could you please share your sample data to me? As by the matrix visual your shared, am not sure about what is your data struct and for the other columns. Did them come from the same table or not? You can uoload your files to one drive and share the link here.