Help with Creating a Measure that FIlters another measure for the last 12 months

[zeppelin77bc]

Hi,

I am trying to take a measure that sums up customers (sumx) and then shows the last 12 months.

My underlying measure is below

Enrollment Count =

VAR MonthStart =

    DATE ( YEAR ( MAX ( 'TimeCalendar'[Date] ) ),

           MONTH ( MAX ( 'TimeCalendar'[Date] ) ), 1 )

VAR MonthEnd   = EOMONTH ( MonthStart, 0 )

RETURN

CALCULATE (

    SUMX ( 'ENR Cube', VALUE ( 'ENR Cube'[Enrolled CT] ) ),

    CROSSFILTER ( 'TimeCalendar'[Date], 'ENR Cube'[Program Start Date], NONE ),

    FILTER (

        ALL ( 'ENR Cube'[Program Start Date], 'ENR Cube'[Program End Date] ),

        'ENR Cube'[Program Start Date] <= MonthEnd

            && COALESCE ( 'ENR Cube'[Program End Date], DATE ( 9999, 12, 31 ) ) >= MonthStart

    )

)

Basically want to create the LTM column from Excel below.  How do I filter the above measure to get my LTM calculation to work correctly? LTM is just a rolling sum of the 12 previous months

Date	Enrollment Count	LTM
7/1/2024 0:00	307	5685
8/1/2024 0:00	57	5499
9/1/2024 0:00	428	5681
10/1/2024 0:00	428	5863
11/1/2024 0:00	428	6045
12/1/2024 0:00	428	6227
1/1/2025 0:00	1044	6422
2/1/2025 0:00	1044	6617
3/1/2025 0:00	1044	6812
4/1/2025 0:00	1058	7022
5/1/2025 0:00	489	7150
6/1/2025 0:00	493	7248
7/1/2025 0:00	262	7203
8/1/2025 0:00	21	7167

[v-sshirivolu]

Hi @zeppelin77bc ,

I would also take a moment to thank @wardy912  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[Royel]

Hi @zeppelin77bc  here are some quick fix 

Enrollment Count LTM = 
CALCULATE (
    [Enrollment Count],
    DATESINPERIOD (
        'TimeCalendar'[Date],
        MAX ( 'TimeCalendar'[Date] ),
        -12,
        MONTH
    )
)

Another options 

Enrollment Count LTM = 
CALCULATE (
    [Enrollment Count],
    DATESBETWEEN (
        'TimeCalendar'[Date],
        EDATE(MAX('TimeCalendar'[Date]), -12),
        MAX('TimeCalendar'[Date])
    )
)

Note: Make sure your 'TimeCalendar'[Date] column must be properly formatted as a Date data type and contain continuous dates. 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[wardy912 · Accepted solution]

Hi @zeppelin77bc 

To calculate a Last Twelve Months (LTM) version of your Enrollment Count measure, you’ll want to wrap it in a CALCULATE that filters the date range to the last 12 months based on your TimeCalendar table.

Enrollment Count LTM =
CALCULATE (
    [Enrollment Count],
    DATESINPERIOD (
        'TimeCalendar'[Date],
        MAX ( 'TimeCalendar'[Date] ),
        -12,
        MONTH
    )
)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[zeppelin77bc]

Thanks. Those really helped.