Demand Forecast formula help

[olivere91]

I'm trying to create a demand forecast for my items. I'm stuck right now on this issue.

I have a measure calculating inventory on hand, which is just a sum formula, and another measure calculating demand which is another sum formula.

I then have a measure: invt = [On Hand] - Demand_Forecasting[Forecasted Sales].

The problem I have is when I put it into a Matrix; the inventory doesn't roll Month to Month. For example, January should be ( On Hand] - Demand_Forecasting[Forecasted Sales] ), and that ending number should be the inventory at the start of February. The February should be the starting number minus the forecasted sales and so on.

[v-yueyunzh-msft]

Hi, @olivere91 

According to your description, you want to calculate the value of scrolling, but based on your text description, I don't quite understand your needs. You can try to provide me with some test data in table form, and provide some of the results you want in table form, and describe your calculation logic to me, which will make the problem more concise and clear.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[olivere91]

Hi,

Does this help? I'm trying to calculate the inventory for an item with i's current on-hand inventory minus it's monthly sales forecast. 

Item	January	February	March
Inventory on hand	5399	4499	3599
minus sales forecast	900	900

[v-yueyunzh-msft · Accepted solution]

Hi, @olivere91   

Thanks for your quick response and sorry for delay response due to my my holiday for several days.
According to your description, you want to get the [current month value] - [next month value] .
Here are the steps you can refer to :
(1)This is my test data:

(2)We need to create a date table as a dimension:

Date = ADDCOLUMNS(CALENDAR(FIRSTDATE('Demand_Forecasting'[Date]), LASTDATE('Demand_Forecasting'[Date])) , "Month" , MONTH([Date]) , "Month_Name" , FORMAT([Date] ,"mmmm"))

 (3)In this visual i put the [My Measure] as a value:

My Measure = SUM('Demand_Forecasting'[On Hand])

(4)Then we can create a measure:

Measure = var _next_month =  CALCULATE([My Measure] , OFFSET(1,ALLSELECTED('Date'[Month_Name],'Date'[Month]) , ORDERBY('Date'[Month] ,ASC) ))
return
IF(_next_month = BLANK() , BLANK() , [My Measure] - _next_month)

(5)Then we can put the fields we need and the measure on the visual and we can get this:

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly