Skip to main content
cancel
Showing results for 
Search instead 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

Reply
TBenders
Helper II
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.   

 

- 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?

1 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]

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Take a look at this:

 

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

 

Anyone who can offer me more insights or direction?

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
Helper II
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:

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors