Forum Discussion
Validate Last 3M DAX query at month level
Hi All,
Please help me on below DAX query calculate for Last 3M .
Last 3M =
VAR MonthsInRange = 3
VAR LastMonthRange =
CALCULATE (
MAX ( 'Calendar Month'[Month Yyyymm] ),
FILTER ( 'Calendar Month', NOT ( ISBLANK ( [Amount] ) ) )
)
VAR FirstMonthRange =
--LastMonthRange - MonthsInRange + 1 --202201 - 3 + 1 = 202198 + 1 = 202199
IF (
MOD ( LastMonthRange, 100 ) < 3,
LastMonthRange - 91 + 1 --include year change calc - 94 + 1 <=> - MonthinRange + 1 - 88
,
LastMonthRange - MonthsInRange + 1
)
VAR Result =
CALCULATE (
[Amount],
REMOVEFILTERS ( 'Calendar Month' ),
REMOVEFILTERS ( 'Calendar Month Prev' ),
'Calendar Month'[Month Yyyymm] >= FirstMonthRange
&& 'Calendar Month'[Month Yyyymm] <= LastMonthRange
)
RETURN
Result
Please let me know if you required more inputs.
Thank You,
1 Reply
- amitchandak
Super User
Anonymous , Create a date with help from a month and year and use TI
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH))
