Measure to get last 3 years

[Anonymous]

Hi all,

I have the below Year table and Year filter from Table1. I need CheckMeasure123 to put 1 next to 2018, 2019, 2020 when i select 2020 from the year filter.  

Any help around this is greatly appreciated. 

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You will need two tables.

Create a simple table as slicer with first formula and create measure with second formula like below.

slicer = DISTINCT('Table'[year])

Measure = IF(SELECTEDVALUE('Table'[year])>SELECTEDVALUE('slicer'[year])-3&&SELECTEDVALUE('Table'[year])<=SELECTEDVALUE(slicer[year]),1,0)

 Result would be shown as below.

Best Regards,

Jay

[AllisonKennedy]

Are these all in the same table? You will need two tables with Year column in each to make this work ideally- 1 table for the year filter. 1 table for the year in the table that is NOT related to the year filter table.

Is there any other requirement to your question? Once you have the relationships set up correctly, the measure part is easier. What if they select both 2019 and 2020 from the slicer?

Try using:
IF(NOT(SELECTEDVALUE(Table[Year]) IN VALUES(Slicer[Year])), 1, 0)

[Anonymous]

@AllisonKennedy Thanks.  Actually they are from the same table. Can this be possible without creating a new table for year alone?

[AllisonKennedy]

Actually, on reading your post more carefully since you're looking for last three years, you could use DATEADD() function or DATESBETWEEN
I still recommend having a DimDate table and a Fact table in this case, but they can be related. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[AllisonKennedy]

@Anonymous  because you want to show items that are NOT selected, you need at least two tables that are NOT related. 

You could create the table using DAX: SlicerYearTable = VALUES(table[year]) or in Power Query.