Filter 12 months previous

[sayang194]

Hi,

I want to count rows for the current month & for 12 months previous (I dont want any data in between). Only for current month & 12 months previous (Eg. May 2022 & May 2021)

I have the below formula that correctly filters for current month, but how can I modify this to filter the data that was 12 months back?

CALCULATE(
COUNT('table1'[ID]),
FILTER(DIM_Kalender, And(YEAR(DIM_Kalender[Date]) = YEAR(MAX('table1'[Date])), MONTH(DIM_Kalender[Date]) = MONTH(MAX('table1'[Date])))
)

[v-zhangti · Accepted solution]

Hi, @sayang194 

Maybe you don't need to modify your DAX function. When you use Table1[Date] as a slicer, you can filter the data directly before December.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JWilde]

Hello,

I think you might be better off using TODAY() and EDATE(). Here's how I set a measure to look for data in a one-week window 12 months prior to a selected date:

I think for what you're trying to do, you could just filter your table1 like so:

CALCULATE(
COUNTROWS('table1'),
FILTER('table1',
MONTH('table1'[Date]) = MONTH(EDATE(TODAY()),-12)
))

[DataInsights]

@sayang194,

Try this measure:

Last Year =
CALCULATE ( COUNT ( 'table1'[ID] ), SAMEPERIODLASTYEAR ( DIM_Kalender[Date] ) )