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

[y5famfnatudu]

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

[FreemanZ]

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.

[y5famfnatudu]

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

[FreemanZ]

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:

[y5famfnatudu · Accepted solution]

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

[FreemanZ]

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]
)