Creating a chart for rolling figure in date format

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,