Rolling 12 month average when data is only monthly

[cathoms]

Hello, I'm trying to create a rolling average for a mortality index. Mortality index is the the relationship between observed and expected deaths. My data set includes number of cases, number of observed deaths, and number of expected deaths by month. To get the mortality index, I first determine what percent of all cases have an observed death and what percent have an expected death. Then the index is determined by dividing % Observed Deaths by % Expected Deaths. DAX looks like this:

Total Cases = SUM( 'Patient Outcomes'[encounters] )
Total Obs Deaths = SUM( 'Patient Outcomes'[deaths] )
Total Exp Deaths = SUM( 'Patient Outcomes'[deathsExp] )
%DeathsObs = DIVIDE( [Total Obs Deaths], [Total Cases] )
%DeathsExp = DIVIDE( [Total Exp Deaths], [Total Cases] )
Mortality Index = DIVIDE( [%DeathsObs], [%DeathsExp], 0.00 )

I have a DateDim table connected to my Patient Outcomes fact table by DschMnStDT (Discharge month start date). Sample data:

dischargeMonth	DschMnStDT	encounters	deaths	deathsExp
2021-10	10/1/2021	1954	59	74.08
2021-11	11/1/2021	1796	83	89.72
2021-12	12/1/2021	1858	82	95.06
2022-01	1/1/2022	1802	88	79.69
2022-02	2/1/2022	1844	73	74.18
2022-03	3/1/2022	2002	58	67.12
2022-04	4/1/2022	1982	81	64.55
2022-05	5/1/2022	2139	59	59.07
2022-06	6/1/2022	1962	58	59.38
2022-07	7/1/2022	2010	62	61.99
2022-08	8/1/2022	2049	65	62.61
2022-09	9/1/2022	2036	64	64.21
2022-10	10/1/2022	1997	52	62.89
2022-11	11/1/2022	2013	64	68.91
2022-12	12/1/2022	2164	60	70.89
2023-01	1/1/2023	2114	52	66.92
2023-02	2/1/2023	1842	56	72.46
2023-03	3/1/2023	2194	57	68.55
2023-04	4/1/2023	2232	62	72.79
2023-05	5/1/2023	2384	66	73.35
2023-06	6/1/2023	2304	59	74.17
2023-07	7/1/2023	2224	53	67.66
2023-08	8/1/2023	2373	59	70.03
2023-09	9/1/2023	2197	50	60.06
2023-10	10/1/2023	2212	61	77.20
2023-11	11/1/2023	2233	60	69.90
2023-12	12/1/2023	2231	69	71.02
2024-01	1/1/2024	1340	33	53.44

I've tried several variations to get a rolling 12 month average of mortality index but my measures keep returning the Mortality Index for each month. Here is mortality index and my expected result:

dischargeMonth	Mortality Index	Expected Outcome
2021-10	0.80
2021-11	0.93
2021-12	0.86
2022-01	1.10
2022-02	0.98
2022-03	0.86
2022-04	1.25
2022-05	1.00
2022-06	0.98
2022-07	1.00
2022-08	1.04
2022-09	1.00	0.98
2022-10	0.83	0.99
2022-11	0.93	0.99
2022-12	0.85	0.99
2023-01	0.78	0.96
2023-02	0.77	0.94
2023-03	0.83	0.94
2023-04	0.85	0.91
2023-05	0.90	0.90
2023-06	0.80	0.88
2023-07	0.78	0.86
2023-08	0.84	0.85
2023-09	0.83	0.83
2023-10	0.79	0.83
2023-11	0.86	0.82
2023-12	0.97	0.83
2024-01	0.62	0.82

[cathoms]

Here are some of my failed attempts, all of which return the exact same values for only nine months and those values match each month's value:

MortIndex R12M = 
VAR _NumMonths = 12
VAR _LastCurrentDate = MAX( DateDim[Date] )
VAR _Period = DATESINPERIOD( DateDim[Date], _LastCurrentDate, - _NumMonths, MONTH )
VAR _Result =
    CALCULATE(
        AVERAGEX( 
            VALUES( DateDim[Month] ),
            [Mortality Index]
        ),
        _Period
    )
VAR _FirstDateInPeriod = MINX( _Period, DateDim[Date] )
VAR _LastDateWithIndex = MAX( 'Patient Outcomes'[DschMnStDT] )

RETURN
    IF( _FirstDateInPeriod <= _LastDateWithIndex, _Result )

MortIndex R12M v3 = 
CALCULATE(
    [Mortality Index],
    DATESINPERIOD( DateDim[Date], MAX( DateDim[Date] ), -12, MONTH )
)

MortIndex R12M v4 = 
    CALCULATE(
        AVERAGEX(
            VALUES( DateDim[Date] ),
            [Mortality Index]
        ),
        DATESINPERIOD( DateDim[Date], MAX( DateDim[Date] ), - 12, MONTH )
    )

MortIndex R12M v5 = 
VAR _NumMonths = 12
VAR _LastCurrentDate = MAX( DateDim[Date] )
VAR _PeriodToUse = 
    FILTER(
        ALL( DateDim ),
        AND(
            DateDim[Date] > _LastCurrentDate - 365,
            DateDim[Date] <= _LastCurrentDate
        )
    )
VAR _Result =
    CALCULATE(
        AVERAGEX( DateDim, [Mortality Index] ),
        _PeriodToUse
    )
RETURN
    _Result

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[cathoms]

Not really an option for a couple of reasons - the file is fairly large and, more importantly, this involves sensitive data that I cannot share.