Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Creating projection from past data dynamically

Hi,

 

I need to make a projection calculation and I'm a bit lost how to do that. 

I have actual data of the leaving employee count per month. I have the below chart with 3 measures:

 

This chart can be dynamically modified by a date slicer. I want to create attrition projections. Attrition is always calculated in a 12 months timeframe.

First, I need to create a measure that is able to show future voluntary leaver projection based on the average of the actuals choosen by the date range slicer. For example, if only 2022-11 and 2022-12 is choosen, we have (48+24)/2=36 leavers per month, which means in the next 10 month we can expect 36 leavers each month,  432 in sum. If 4 months are selected in the date range slicer, the projection only goes for the next 8 months and each month the leaver count is the avg of this 4 month. 

If more that 12 months is selected only the leaver count will be shown, the attrition projection is not applicable.

Based on this the attrition projection will be easy to do.

Can you advise what functions to use to achive that?

Thanks, 

 

Need Help

3 Replies

  • zzzsharepoint's avatar
    zzzsharepoint
    Helper I
    3 years ago

     

    To create an attrition projection based on the selected date range in the slicer, you can use a combination of the SUMX and AVERAGE functions in Power BI.

    Here is an example of how you can create a measure for future voluntary leaver projection:

    1. Create a measure that calculates the average leavers per month based on the selected date range in the slicer:

       

      Leavers per Month = AVERAGE(FILTER(table,table[date] >= MIN(slicer[date]) && table[date] <= MAX(slicer[date])),table[leavers])

       

      The above measure filters the table based on the selected date range in the slicer and calculates the average of the leavers count.

      1. Create another measure that calculates the total number of future voluntary leavers based on the average leavers per month and the number of future months.

         

        Future Voluntary Leavers = SUMX(FILTER(MONTH(TODAY())+1+RANGENUMBER(10,1,0),[months]<=12),[Leavers per Month])

         

        The above measure uses the SUMX function to sum the leavers per month for the next 10 months, but also uses a filter to check if the leaver projection will be based on the actuals within 12 months only and not more. If more than 12 months is selected in the date slicer, the result will be the actuals count and not the projection.

        This should give you the total number of future voluntary leavers based on the average leavers per month and the number of future months. You can then use this measure to create a chart or table to visualize the projections.

        You can also create similar measure for the Attrition projection.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi there,

       

      Thanks for the quick feedback!

      What is RANGENUMBER? Power BI does not seem to recognize this function.

      Thanks!

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I've created this measure based on your suggestion:

     

    Future External Leaver projection = SUMX(FILTER(FILTER('Dim Date','Dim Date'[Date]>= TODAY()),[Num of Months in period]<=12),[Avg Leavers per period])
     
    But unfortunately this measure is not showing up for future timepoints (that's the part I'm really clueless how to achieve!)