Display data for past 3 years from selected date in slicer

[adityah]

I'm trying to create a date filter which will filter past 3 years data based on selection in a PBI matrix. The columns are the years. Below is a screenshot of how the marix looks like:

So if I select 2024 in date filter, it should display 2024, 2023 and 2022. if I select 2023, it should display 2023,2022, 2021. I realise there are previous posts similar to this, but they dont seem to work for matrix since I need to put Year in columns instead of values (previous results suggest creating a measure, which cannot go into pbi matrix column. only in values)

I have a separate date table which is related to all the other tables

[v-yajiewan-msft]

Hi @adityah , hello All, thank you for your prompt reply!

Create a new calculated Date table as the slicer, then create a new measure as shown below:

past 3 years = 
VAR data_year = YEAR(SELECTEDVALUE('FactTable'[date]))

VAR slicer_year = SELECTEDVALUE('DuplicateDate'[Date].[Year])
RETURN
IF(data_year<=slicer_year && data_year>slicer_year-3,
    1,
    0
)

Then add the measure to visual filter like this:

Result for your reference:

Best regards,

Joyce

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

[Kedar_Pande]

@adityah 

Create a new measure

Filtered Endorsements = 
VAR SelectedYear = SELECTEDVALUE(DateTable[Year])
RETURN
CALCULATE(
SUM('YourDataTable'[Endorsements]), 
FILTER(
ALL(DateTable),
DateTable[Year] >= SelectedYear - 2 && DateTable[Year] <= SelectedYear
)
)

💌 If this helped, a Kudos 👍 or Solution mark ✔️would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[adityah]

This wont work. since I need to bring it in columns. You cannot bring a metric into matrix columns

[Rupak_bi]

hi @adityah ,

To achieve this, follow below process
1. create a separate calender dimention table covering all the dates in your dataset. dont create any relationship. 

2. use this date in your slicer

3. now you need to create three different measures for "new", "endrosment","GWP" as per below syntax.

measure = 
var start_year_Base =year( selectedvalue(dim_calender[date])]) - 2

var end_year_Base =year( selectedvalue(dim_calender[date])])
return
calculate (sum(table,[GWP]),year(table[date])>=start_year_base,year(table[date])<=end_year_base)

This should work. please share sample data if it fails.

[johnt75]

Have a look at https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ . By putting 'Previous Dates'[Year] in the matrix you should get the behaviour you are looking for.