Creating a chart for rolling figure in date format

[Andonip]

Hi all,

I have built a employee turnover metric that looks at average headcount and total leavers in a 12 month timeframe. To get the % figures I have built a Date Table that when I slide the start and end period of each month it returns me the complete breakdown of the data I need.

Rolling Turnover % (Total) =

DIVIDE([Rolling Leavers (Total)],[Avg Monthly Headcount (12M)],0)

However, when reporting on trends it makes it difficult as I have to move the date table periods each time to the 12 month outlook I need. For example for December 2024 I need to put 01/01/2024 - 31/12/2024, for January 2025 it would be 01/02/2024 - 31/01/2025.

The Date Table is linked to my main staff file, which has each month of the period 01/2024, 02/2024 etc, and then linked also to my exits table that has the last day per individual. 

This gives me exactly what I need but it would be very helpful to try and investigate if there is a way to add to this to be able to build some charts over time to better understand trends. 

Appreciate I may be lacking a lot of info still required, will try my best to share anything that is not clear.

Kind regards,

[v-kpoloju-msft]

Hi @Andonip,
 
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @DataNinja777, for his inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[DataNinja777]

Hi @Andonip ,

Yes, you can definitely create a trend chart for your rolling 12-month turnover. The issue you're facing is that your current measures are reacting to the slicer, but for a trend chart, the calculation needs to be dynamic for each point on the chart's axis. This is achievable by correcting your data model and writing new DAX measures that use time intelligence functions.

First, you need to adjust your data model. DAX time intelligence functions require active relationships based on a proper date column, not a text field like your Period column. In your model, you should delete the relationships based on the Period field and then create (or activate) relationships from your DateTable[Date] column to the corresponding date columns in your other tables (Headcount[Last Day of Month] and Terminations[Event Date]). Also, be sure that your DateTable is marked as the official date table in your model settings, as this is crucial for the DAX calculations to work correctly.

With the model corrected, you can create a few new DAX measures. It's best to start with simple base measures for total leavers and end-of-period headcount. Then, you can build the measures that perform the rolling 12-month calculations dynamically. Finally, you'll combine them into your ultimate turnover percentage measure.

// Counts total terminations
Total Leavers = COUNTROWS('Terminations')

// Calculates headcount for the last date in the current context (e.g., end of a month)
Headcount EOP = 
CALCULATE(
    DISTINCTCOUNT('Headcount'[EmployeeID]), -- Or your specific headcount logic
    LASTDATE('Headcount'[Last Day of Month])
)

// Calculates the sum of leavers over the previous 12 months from the current date context.
Rolling 12M Leavers = 
CALCULATE(
    [Total Leavers],
    DATESINPERIOD(
        'DateTable'[Date],
        MAX('DateTable'[Date]),
        -12,
        MONTH
    )
)

// Calculates the average of the month-end headcounts over the previous 12 months.
Rolling 12M Avg Headcount = 
VAR LastDate = MAX('DateTable'[Date])
VAR DateRange = DATESINPERIOD(
                    'DateTable'[Date],
                    LastDate,
                    -12,
                    MONTH
                )
RETURN
AVERAGEX(
    VALUES('DateTable'[YearMonth]), -- Assumes you have a YearMonth column like "2024-01"
    CALCULATE([Headcount EOP], DateRange)
)

// Final turnover percentage measure.
Rolling 12M Turnover % = 
DIVIDE(
    [Rolling 12M Leavers],
    [Rolling 12M Avg Headcount],
    0
)

After creating these measures, building the chart is simple. Add a Line Chart to your report. Drag a date field from your DateTable to the X-axis and your new [Rolling 12M Turnover %] measure to the Y-axis. You will now see a trend line showing your rolling 12-month turnover, with each point correctly calculated for the 12 months leading up to it, eliminating the need to manually adjust the slicer for trend analysis.

Best regards,