cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## HR / Revenue Forecast with Seasonal Correction - need help

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.

 - EmployeesIDProject Start DateProject End DateAverage Revenue per MonthAverage Cost per Month - Example data10000101-01-2018 (Jan 1st)31-09-2018 (End of Okt)50004000 - CalendarDateYearMonthDay - Example data01-01-201820180101 - Seasonal CorrectionsFirst Day of MonthYearMonthShare of Billable Hours (factor of year) - Example data01-01-20182018010,089(which means Jan is above average, as the average per month is1/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?

1 ACCEPTED SOLUTION
Helper II

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]

5 REPLIES 5
Super User

Take a look at this:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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)...

Helper II

Anyone who can offer me more insights or direction?

Helper II

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]

Helper II

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:

 EmployeesIDProject Start DateProject End DateAverage Revenue per MonthAverage Cost per Month Example data10000101-01-2018 (Jan 1st) 31-09-2018 (End of Okt)50004000 CalendarDateYearMonthDay Example data01-01-201820180101 Seasonal CorrectionsFirst Day of MonthYearMonthShare of Billable Hours (factor of year) Example data01-01-20182018010,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?