Problem with Rolling 12 month turnover DAX

[MatiasM]

Hi Community,

I hope you can help with this query.

I'm trying to get the rolling 12 month data of turnover in my Company. The formula seems to be working fine when I don't filter by specific months (see below)

Turnover (% Rolling 12 months) =

    CALCULATE (

        [Turnover (%)],

        DATESINPERIOD ( Overall[Last Day of Duty], MAX(Overall[Last Day of Duty]), -12, MONTH )

    ) / 12 // 12 months ending at the end of the current filtered month

Turnover % is the filtering formula that I use to get the right population=

Turnover % =

VAR FormulaTurnover =

    CALCULATE (

    DISTINCTCOUNT(Overall[Staff Member]),

        Overall[Termination Reason] IN {"RESIGNATION","RETIREMENT"}

    // Overall[Termination Reason] = "RESIGNATION" || Overall[Termination Reason] = "RETIREMENT"

    // OR (  Overall[Termination Reason] = "RESIGNATION" , Overall[Termination Reason] = "RETIREMENT")

    )

RETURN

DIVIDE ( FormulaTurnover, DISTINCTCOUNT ( Overall[Staff Member] ) )

The challenge is when I try to filter by a specific month where I don't have any people leaving the company which means =
If Turnover % for a specific month is 0 then rolling 12 month returns 0. This is not what I need because, regardless of having one month with no records I still have the previous months with data.

I hope I'm clear with what I need, let me know if you require further information

Thanks in advance

Matias

[ThxAlot]

No instructive help can be offered without concrete mock-up dataset. But generally speaking, a standard calendar table helps avoid quite some pitfalls when time intelligence functions are used (eg  DATESINPERIOD ( Overall[Last Day of Duty], MAX(Overall[Last Day of Duty]), -12, MONTH ) in your case); in fact, here both MAX() and DATESINPERIOD() are subject to current filter context.