cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Thanks!

1 ACCEPTED SOLUTION
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?

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

3 REPLIES 3
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:

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.

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?

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors