cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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!:
Mastering Power BI 2nd Edition

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?

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors