cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
William84
Regular Visitor

Forecast method

Hi everyone, i am struggling to calculate a forecast and i was hoping someone here could help me.

I Have the dates for the entire month, the plan (S&OP) for the month (daily) and the accumulated plan . I also have the  measures per day and the accumulated measures per day.

William84_2-1660175979838.png

 

I was required to create two forecast, the first one i was able to do it , as the logic is based on the average of the daily actual, would be something like: 

Forecast Case 1 Average:

  1. Day 1: Actual accumulated + Average
  2. Day 2 = Day 1 + Average -= Actual + 2*Average
  3. Day3 = Day2 + Average = Actual + 3* Average
  4. Etc.

And I used the datediff to get the column of numbers 1,2,3,4 etc. until the end of the month, and the dax i used is: 

William84_1-1660172279741.png

So i calculated different variables for the end of the month, the production day ( the current day of the month), etc 

and then i used the logic to apply : "Actual + Average*daydiff

 

My problem is the forecast 2, for the S&OP when the S&OP changes, as would be:

  1. Day1: Actual + S&OP1
  2. Day2: Day1+ S&OP2 = Actual+S&OP1+S&OP2
  3. Day3: Day2+S&OP3 = Actual +S&OP1+S&OP2+S&OP3
  4. Etc.

As in this case the S&OP is different everyday, cannot apply the logic as in the case 1, and I would need to iterate/for loop in each row… and I have trying to check that for few hours and I am getting a bit stuck..

 

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@William84 , Based on what I  got

One of the method

 

Assume Actual is a measure

 

[Actual] + calculate(Sum(Table[S&OP]), filter(all(Table), Table[Date]<= Max(table[Date]) ) )

 

or actual last non blank

 

lastnonblankvalues(Table[Date], [Actual])  + calculate(Sum(Table[S&OP]), filter(all(Table), Table[Date]<= Max(table[Date]) ) )

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@William84 , Based on what I  got

One of the method

 

Assume Actual is a measure

 

[Actual] + calculate(Sum(Table[S&OP]), filter(all(Table), Table[Date]<= Max(table[Date]) ) )

 

or actual last non blank

 

lastnonblankvalues(Table[Date], [Actual])  + calculate(Sum(Table[S&OP]), filter(all(Table), Table[Date]<= Max(table[Date]) ) )

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.