March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Solved! Go to 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] )
)
)
)
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.
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] )
)
)
)
@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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
85 | |
76 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
55 |