How to get the last 12 months YearMonth using a FILTER inside SUMMARIZECOLUMNS

[Anonymous]

Hi,

I need to get the YearMonths for the last 12 months and I used the following query but unfortunately it shows all year months:

Evaluate

SUMMARIZECOLUMNS(
'Date'[Year Month],
FILTER(
ALL('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Date] >= DATEADD('Date'[Date],-12,MONTH)
),
"Val",[Value]
)
ORDER BY [Year Month] DESC

Note that I added Val just to get the Max Date with Value and not get the Max Date which is 2050/12. The Max Date with data is 2021/01. Using the above code would yield to a table with all YearMonths on and before 2021/01. I need only the last 12 months before 2021/01. Thanks in advanced.

[amitchandak]

@Anonymous , PLease try a measure like this with date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-12,MONTH))

[Anonymous]

Unfortunately, I need a FILTER within the SUMMARIZECOLUMN to filter the Date or Year Month up to the last 12 months only. I tried using the measure you provided and put it below as a measure but it still displays data from 2017 up to 2021/12 instead of only 2020/01 to 2021/01.

[amitchandak]

@Anonymous , try like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-12,MONTH))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(today(),0),-13,MONTH))

this should work with summarize columns

[Anonymous]

Sorry but that didnt seem to work as well 😞 I need the FILTER on my SUMMARIZECOLUMNS. Tried using the CALCULATE measure on my Val but it still displays beyond the 12 months.

[amitchandak · Accepted solution]

@Anonymous , Try this.

SUMMARIZECOLUMNS(
'Date'[Year Month],
FILTER(
ALL('Date'[Date]),'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Date] >= date(year(today()), month(today())-12,day(today()))
),
"Val",[Value]
)
ORDER BY [Year Month] DESC

[Anonymous]

That worked! Awesome, thanks a lot! @amitchandak 🙂