Forum Discussion

zeppelin77bc's avatar
zeppelin77bc
Icon for Helper I rankHelper I
11 months ago
Solved

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

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
DateEnrollment CountLTM 
7/1/2024 0:003075685
8/1/2024 0:00575499
9/1/2024 0:004285681
10/1/2024 0:004285863
11/1/2024 0:004286045
12/1/2024 0:004286227
1/1/2025 0:0010446422
2/1/2025 0:0010446617
3/1/2025 0:0010446812
4/1/2025 0:0010587022
5/1/2025 0:004897150
6/1/2025 0:004937248
7/1/2025 0:002627203
8/1/2025 0:00217167
 
Need Help
  • wardy912's avatar
    wardy912
    11 months ago

    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!

4 Replies

  • Royel's avatar
    Royel
    Icon for Super User rankSuper User
    11 months ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    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

  • wardy912's avatar
    wardy912
    Icon for Super User rankSuper User
    11 months ago

    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!