Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Accumulated Last 12 month with a twist

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

PBISea_0-1625221413127.png

 

Thanks!

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

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

Vera_33_0-1625310547926.png

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] )
            )
        )
    )

 

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_1-1625546783752.png

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
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

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

Vera_33_0-1625310547926.png

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
Not applicable

@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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.