topic Get records from current & previous year, when only current year selected in DAX Commands and Tips

Get records from current & previous year, when only current year selected

y5famfnatudu — Thu, 02 Feb 2023 15:04:22 GMT

Hello,

I would appreciate it so much if you can help me here please.

I would like to select a year from a slicer, but the records from the Selected year & records from Previous year to be shown. How can I achieve something like that?

Best regards,

Simon

Re: Get records from current & previous year, when only current year selected

FreemanZ — Thu, 02 Feb 2023 15:16:43 GMT

hi y5famfnatudu

you can try like:

Measure =

CALCULATE([...], EDATE(TableName[Date], -12))

or if you have dedicated date table, try Time Intelligence functions, like:

CALCULATE([...], DATEADD(TableName[Date], -1, YEAR))

CALCULATE([...], SAMEPERIODLASTYEAR(TableName[Date]))

and many more.

Re: Get records from current & previous year, when only current year selected

y5famfnatudu — Thu, 02 Feb 2023 16:42:34 GMT

Hi FreemanZ ,

But I don't need measures to calculate anything, I just need a table visual to show the records belonging to the selected year & previous year, as shown in the screenshot above.

Thank you,

Simon

Re: Get records from current & previous year, when only current year selected

FreemanZ — Fri, 03 Feb 2023 02:01:00 GMT

hi y5famfnatudu

supposing you have two unrelated tables like:

1) plot a table visual with all the columns from the first table, choose "don't summarize" for every column.

2) write a measure like:

YearFilter = VAR _year = SELECTEDVALUE(Year[Year]) RETURN IF( YEAR(MAX(TableName[OrderDate])) IN {_year, _year-1}, 1,0 )

3) feed the measure to the filter pane for the table visual and choose 1

it worked like this:

Re: Get records from current & previous year, when only current year selected

y5famfnatudu — Fri, 03 Feb 2023 06:12:37 GMT

Thank you so much FreemanZ

Unfortunately, this didn't work for me, as the Date Table is realted to Orders, and other tables in the dashboard.

Do you think it's still possible to tweak the measure please so that it shows the selected year and the previous one? Your help is so much appreciated.

Best regards,

Simon

Re: Get records from current & previous year, when only current year selected

FreemanZ — Fri, 03 Feb 2023 06:46:38 GMT

hi y5famfnatudu

The measue could be tweaked to ignore the slicer, but you still have orderdate columns, which are anyway filtered by the Year slicer.

So in cases like this, try to create an small independent table for the year slicer.

You may import one from excel, create one from Power Query Editor, or with DAX code like:

Year = SELECTCOLUMNS( {2022, 2023}, "Year", [Value] )