cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sayang194
Frequent Visitor

Prediction for next 3 monts

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.

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

 

sayang194_0-1659963687130.png

 

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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.

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?

 

sayang194_0-1659963687130.png

 

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

 

MFelix_0-1660403025133.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



sayang194
Frequent Visitor

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.