topic Re: Last 12 months based on Year and Month Name selection in DAX Commands and Tips

Last 12 months based on Year and Month Name selection

amehrosh — Fri, 10 Feb 2023 09:00:14 GMT

Hi.

I have a requirement wherein client wants to see last 12 months data based on the selected Year and Month Name selection.

Suppose if I select 2023 in Year and Month Name as Jan, then it should show us the data from Jan 2023 to Feb 2022 i.e.12 months.

Any help is appreciated.
Thanks,
Alina

Re: Last 12 months based on Year and Month Name selection

FreemanZ — Fri, 10 Feb 2023 09:54:07 GMT

hi amehrosh

it seems you expect a measure. How do you plan to showcase this measure? What context do you have, like axis, row/column of a visual?

Re: Last 12 months based on Year and Month Name selection

amehrosh — Fri, 10 Feb 2023 10:08:06 GMT

measure to show in a matrix.

Re: Last 12 months based on Year and Month Name selection

Anonymous — Mon, 13 Feb 2023 03:08:15 GMT

Hi amehrosh ,

Please create measure with below dax formula:

Measure = VAR cur_year = SELECTEDVALUE ( 'Table 2'[Year] ) VAR cur_month = SELECTEDVALUE ( 'Table 3'[Month] ) VAR _month = SWITCH ( cur_month, "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6, "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12 ) VAR max_date = DATE ( cur_year, _month, 1 ) VAR tmp = DATESINPERIOD ( 'Table 4'[Date], max_date, -12, MONTH ) VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) RETURN IF ( cur_date IN tmp, 1 )

Add a matrix visual and add this measure to filter pane and apply:

For more details, please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.