Creating projection from past data dynamically

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.