HR / Revenue Forecast with Seasonal Correction - need help

[TBenders]

Hi,

Can someone help me factor in seasonal fluctuations in my forecasting below?

Situation: We outsource people to our clients and bill our clients per hour worked.
I have 1 table with our employees and the start/end date of the project.
Another table contains the share of the billable hours each month of the year has.

For example, we expect people in January to have more billable hours than December, because of people taking more days off and more national free days in December.   

- Employees ID Project Start Date Project End Date Average Revenue per Month Average Cost per Month	- Example data 100001 01-01-2018 (Jan 1st) 31-09-2018 (End of Okt) 5000 4000
- Calendar Date Year Month Day	- Example data 01-01-2018 2018 01 01
- Seasonal Corrections First Day of Month Year Month Share of Billable Hours (factor of year)	- Example data 01-01-2018 2018 01 0,089(which means Jan is above average, as the average per month is 1/12=0,083 of year)

So what I’d like to see for Januari as a expected revenue is not 5000, but 5000 * 12 * 0,089 = 5340.

• What’s working: Plotting a monthly forecast based on the duration of the contract
  (fe: before/after contract dates this candidate does not return results, and the months the candidate does work are distributed by months).
  
  Measure:
  FORECAST Revenue =
    VAR currentDate =
    MAX ( 'Calendar'[Date])
      RETURN
         CALCULATE (
          SUM('Employees'[Average Revenue per Month]);
          FILTER (
              Employees;
              ( 'Employees'[Project Start Date] <= currentDate
                  && 'Employees'[Project End Date] >= currentDate )
          )
      )
  
  
• What’s not working:
  I havent been able to get the monthly corrections to work. 

Can you help me with this?

[Greg_Deckler]

Take a look at this:

https://community.powerbi.com/t5/Community-Blog/Correlation-Seasonality-and-Forecasting-with-Power-B...

[TBenders]

Interesting @Greg_Deckler, thanks for pitching in!

I'm having a hard time getting the avarage for each employee to get changed per month by the change factor. My Calendar does not have a relationship with the Employee table.  
I had already taken part of the steps you mention and added [MonthOfYear] to my Calendar and Seasonal Corrections tables to create a relationship.

However, I cannot add this to the Employee table because it's not based on one-row-per-month but has the month-range embedded in a single row (Start Date, End Date)...

[TBenders]

Anyone who can offer me more insights or direction?

[TBenders · Accepted solution]

Think I found a way around it. I added end of the month dates to the monthly correction table and then made a new measure [Forecast Correction], similar measure like the one I mentioned in my original post, but for the correction factor.  
Then a final measure: Corrected Revenue Forecast = [FORECAST Revenue] * 12 * [Forecast Correction]

[TBenders]

Hi,

Can someone help me factor in seasonal fluctuations in my forecasting below?

Situation: We outsource people to our clients and bill our clients per hour worked.
I have 1 table with our employees and the start/end date of the project.
Another table contains the share of the billable hours each month of the year has.
For example, we expect people in January to have more billable hours than December, because of taking holidays and national free days in December.

I have the following tables:

Employees ID Project Start Date Project End Date Average Revenue per Month Average Cost per Month	Example data 100001 01-01-2018 (Jan 1st) 31-09-2018 (End of Okt) 5000 4000
Calendar Date Year Month Day	Example data 01-01-2018 2018 01 01
Seasonal Corrections First Day of Month Year Month Share of Billable Hours (factor of year)	Example data 01-01-2018 2018 01 0,089 (which means Jan is above average, as the average per month is 1/12=0,083 of year)

So what I’d like to see for Januari as a expected revenue is not 5000, but 5000*12*0,089=5340.

• What’s working: Plotting a monthly forecast based on the duration of the contract
  (fe: before/after contract dates this candidate does not return results, and the months the candidate does work are distributed by months).
  
  Measure:
  FORECAST Revenue =
    VAR currentDate =
    MAX ( 'Calendar'[Date])
      RETURN
         CALCULATE (
          SUM('Employees'[Average Revenue per Month]);
          FILTER (
              Employees;
              ( 'Employees'[Project Start Date] <= currentDate
                  && 'Employees'[Project End Date] >= currentDate )
          )
      )
  
  
• What’s not working:
  I havent been able to get the monthly corrections to work. 

Can you help me with this?