Prediction for next 3 monts

[sayang194]

I have a linechart that displays headcount of our company, the chart has data upto the current month. I want this line to continue as a dotted line that displays the headcount prediction for next 3 months. I want to do this by taking the current months headcount and then adding the new hires & subtracting the terminations to get headcount for next month. This has to be dynamic.
For example, If July has HC of 1000, and August has 100 new hires & 50 terminations, then HC for August will be 1050. Now to calculate for september, 1050 will now become the base and we then add & subtract the new hires & terminations of september to get the HC for September.
Any idea how this can be done. My x axis will have date fields (months-Year). Y axis will have HC growth month over month & HC prediction for next 3 months.

[v-yalanwu-msft]

Hi, @sayang194 ;

Can you share your data structures?, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sayang194 · Accepted solution]

Hi, I have following table, and I want to create a measure that will calculate the blank fields in Column1. The calculation should be like for September= Column1[August]+Column2[September]. In this case the value of Column1[September] will be 2967.86. Then for Column1[October]= Column1[September]+ Column2[October], in this case it will 2967.86+30.23. Similarly for rest of the months. Any help will be really appreciated?

[MFelix]

Hi @sayang194 

Try the following measure:

Total Overall =
VAR lastvalue =
    SUMX (
        TOPN (
            1,
            FILTER (
                SUMMARIZE (
                    ALL ( 'Table (2)' ),
                    'Table (2)'[Date].[Year],
                    'Table (2)'[Date].[MonthNo],
                    "@yearmonth", 'Table (2)'[Date].[Year] & FORMAT ( 'Table (2)'[Date].[MonthNo], "00" ),
                    "@Value", SUM ( 'Table (2)'[Column1] )
                ),
                [@Value] <> BLANK ()
            ),
            [@yearmonth], DESC
        ),
        [@Value]
    )
VAR MAXIMUMDATE =
    MAXX (
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Column1] <> BLANK () ),
        'Table (2)'[Date]
    )
RETURN
    IF (
        SUM ( 'Table (2)'[Column1] ) = BLANK (),
        lastvalue
            + CALCULATE (
                SUM ( 'Table (2)'[Column2] ),
                FILTER (
                    ALL ( 'Table (2)' ),
                    'Table (2)'[Date] > MAXIMUMDATE
                        && 'Table (2)'[Date] <= MAX ( 'Table (2)'[Date] )
                )
            ),
        SUM ( 'Table (2)'[Column1] )
    )

[sayang194]

I am using the below measures, but the problem is, for all future months it is still taking the current month as base. For example even for september's prediction it is taking July as base and not the new August prediction value.
How can I solve this?

Last Month FTE =

VAR _lastFTEdate= CALCULATE(MAX(FCT_All_Active_Employees_Effective_Date[Merged Query Date]),ALL(FCT_All_Active_Employees_Effective_Date))

RETURN

CALCULATE(MAX(Dim_Dates[Start of Month]),ALL('Dim_Dates'),Dim_Dates[Date]=_lastFTEdate)

FTE Projection =

VAR _lastFTEMonth = 'Measure Table'[Last Month FTE]

VAR _lastMonthTotal= CALCULATE(SUM(FCT_All_Active_Employees_Effective_Date[FTE]),ALL('Dim_Dates'),Dim_Dates[Start of Month]=_lastFTEMonth)

VAR _thisMonth= MAX(Dim_Dates[Start of Month])

RETURN

IF(_thisMonth<=_lastFTEMonth,

    SUM(FCT_All_Active_Employees_Effective_Date[FTE]),

        _lastMonthTotal+Fct_Active_and_terminated_Employees[Starters]-Fct_Active_and_terminated_Employees[Total Terminations])