Accumulated Last 12 month with a twist

[Anonymous]

Hi all,

I have a task that I'm struggling with to get to work. What I would like is to have Accumulated last 11 months by period. This is not an issue, simply with 

LTM Amt = Calculate([Amt], 
            Datesbetween('Calendar'[Date], edate(min('Calendar'[Date]),-11),max('Calendar'[Date])))

However there something special with 2019 numbers that needs a fix. Technically there are only 6 periods in this year and two by two periods are combined. 
Example when calculating january 2020 numbers I would like to have the first period of 2019 (Jan & Feb) divided by 2 and then the sum of remaining periods. 

However for february i just want the sum of the last 11 periods. 

This goes on every other month until 2020, then its normal 12 period year.

Can this be solved somehow?

I'm adding a sample pbix file and screenshot of what I'm trying to accomplish below:
Pbix file: https://ufile.io/zwxgnt2d

Thanks!

[v-angzheng-msft]

Hi, @Anonymous 

I created a measure like this:

Result = 
IF (
    YEAR ( MAX ( [Date] ) ) >2019,
   
    IF (
        MOD ( MONTH ( MAX ( [Date] ) ), 2 ) = 0,
        CALCULATE (
            SUM ( [Sum] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date] <= EOMONTH ( MAX ( [Date] ), 0 )
                // EOMONTH ( [Date], 0 ) <= EOMONTH ( MAX ( [Date] ), 0 )
                    && 'Table'[Date] > EOMONTH ( MAX ( [Date] ), -12 )
            )
        ),
        CALCULATE (
            SUM ( [Sum] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date] <= EOMONTH ( MAX ( [Date] ), 0 )
                    && 'Table'[Date] > EOMONTH ( MAX ( [Date] ), -11 )
            )
        )
            + CALCULATE (
                AVERAGE ( [Sum] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Date] <= EOMONTH ( MAX ( [Date] ), -11 )
                        && 'Table'[Date] > EOMONTH ( MAX ( [Date] ), -13 )
                )
            )
    )
)

 result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33 · Accepted solution]

Hi @Anonymous 

How did you calculate 3059.5 for 202001? From your explaination, it should be (194+181)/2+SUM(B4:B14)=2962.5?

LTM Amt = 
VAR CurYM =
    SELECTEDVALUE ( 'Calendar'[Month Year Name Sort] )
RETURN
    IF (
        CurYM IN { 202001, 202003, 202005, 202007, 202009, 202011 },
        CALCULATE (
            [Amt],
            DATESBETWEEN (
                'Calendar'[Date],
                EDATE ( MIN ( 'Calendar'[Date] ), -12 ),
                EDATE ( MIN ( 'Calendar'[Date] ), -11 )
            )
        ) /2
            + CALCULATE (
                [Amt],
                DATESBETWEEN (
                    'Calendar'[Date],
                    EDATE ( MIN ( 'Calendar'[Date] ), -10 ),
                    MAX ( 'Calendar'[Date] )
                )
            ),
        CALCULATE (
            [Amt],
            DATESBETWEEN (
                'Calendar'[Date],
                EDATE ( MIN ( 'Calendar'[Date] ), -11 ),
                MAX ( 'Calendar'[Date] )
            )
        )
    )

[Anonymous]

@Vera_33 , sorry, calculation error on my part. Used (B2 + B3/2) instead of (B2+B3)/2. You'r solution is correct. Thank you so much for your help!