Using DATESINPERIOD to calculate a rolling 12 month measure

[nebarnes]

Hi, I am trying to calculate a rolling sickness absence rate over a 12 month period, but only show it for the months of the current financial year (April 2024 shows the total absence rate for May 2023 to April 24, but only April 2024 should be visible on the graph, and so on).

I've got the measure working using DATESINPERIOD but when I plot this on a graph, the line extends beyond the currently selected filter period. All the date filter does is adjust the period that the measure works for, leaving a straight line before and after the selected date period.

This is the measure:

Sick Absence Rolling 12 Months = 
VAR LastSelectedDate = MAX('Calendar'[Date])
VAR TotalSick =
    CALCULATE('People'[Total Sick Days], DATESINPERIOD('Calendar'[Date], LastSelectedDate, -12, MONTH))
VAR TotalDays =
    CALCULATE('People'[Colleague Days], DATESINPERIOD('Calendar'[Date], LastSelectedDate, -12, MONTH))
VAR Result =
    TotalSick/TotalDays
RETURN Result

This is the result on the graph, I want to filter out the entries either side of the red lines and be left with just the results for months within the last financial year:

In the pic above, I am using a filter on the main calendar table to select just the current financial year but no matter what I do, I cannot get rid of the before and after lines.

This is the model, its nothing complicated. People.Month = Calendar.Date

Any help appreciated!

[nebarnes · Accepted solution]

I fixed it myself. If using Power BI's hierarchical date option, then the lines won't go away no matter what. Changing to standard dates fixed it 😑