Showing data of last 2 years & next 2 years of the selected year on slicer

[harshadrokade]

I have 4-5 visuals on a dashboard from Sales Data table. I have a year slicer on the same dashboard. I have sales data in a visual & I want that it should show data of last 2 years of slicer selected year, slicer selected year & next 2 years of slicer selected year. Means If the slcier has year selected as 2019, the visual should show data of 2017, 2018, 2019, 2020 & 2021.

I have data as below-

Sales Data table

Column 1- Order date

Column 2- Order Year

Column 3- Sales

I tried below measure but it is not filtering other visuals. I want that the sales data should reflect of previous 2, current & future 2 years but other visuals should also change & show data of slicer selected year.

So far...

I created a calendar table as below & connected its Date column with Order date column of Sales Data table.

• Date = CALENDAR(MIN(Parameters_New[CY Date]),MAX(Parameters_New[CY Date]))
• Year = YEAR('Calendar 5yr data'[Date])
• Month = FORMAT('Calendar 5yr data'[Date],"mmmm")

I created a year table as below

• Year = This column has year list from 1990 to 2050

Then I created a mesure as 

•  5 yr sales data = CALCULATE(SUM(Sales Data table[Sales]),FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Year'[Year])+2&&'Calendar'[Year]>=SELECTEDVALUE('Year'[Year])-2))

 The above is not helping me to filter 5 years sales data of slcier year + to filter other visuals based on slicer year. Pls help.

[Anonymous]

Hi @harshadrokade 

When you said the above was not helping, what did it return? I don't see you can put SELECTEDVALUE in FILTER like this. Your slicer 'Year'[Year] is a disconnected one from your Sales Data table?

5 yr sales data = 
VAR CurYear = SELECTEDVALUE('Year'[Year])
RETURN
CALCULATE(SUM(Sales Data table[Sales]),FILTER('Calendar','Calendar'[Year]<= CurYear+2 &&'Calendar'[Year]>=CurYear-2))

[harshadrokade]

Hi @Anonymous 

Thanks a lot for your help.

It returned correct sales values as expected when I was using year values from Year table in slicer. Like when i selected yeat 2019, it gave me data of 2017, 2018, 2019, 2020 & 2021 however it has not changed the value of other visuals which data is derived from Sales data table only. I added year values from Sales table in slicer (after connecting Sales data table with Year column of Year table) but then it works on other visuals but then it doesn;t provide me Sales data of last & next two years.

[Anonymous]

Hi @harshadrokade 

If I understand it correctly, the Year table is disconnected, then it works in the 5-year sales, but other visuals won't work; if it is connected, then other visuals work but 5-year sales not. How about using your connected Calendar instead?

5 yr sales data = 
VAR CurYear = SELECTEDVALUE('Calendar'[Year])
RETURN
CALCULATE(SUM(Sales Data table[Sales]),FILTER(ALL('Calendar'[Year]),'Calendar'[Year]<= CurYear+2 &&'Calendar'[Year]>=CurYear-2))

[harshadrokade]

Thanks a lot @Anonymous for your reply. I tried this on my file but then it provides value of the single year. Will u be able to explain this in a PBIX file, only if you have time 🙂

[Anonymous]

Hi @harshadrokade 

Agree with @aj1973 , it is better that you share a sample .pbix file. My dummy data is simply a fact table with a connected Calendar table, nothing else. Not sure why the measure not working in your model as we don't know your model.

[harshadrokade]

@Anonymous Actually Mam, I tried uploading file earlier but my organisation doesn;t allow me to uplaod the files in external portal & so I can;t do that mam. It will be very helpful if you spare sometime & share apBIX file with some dummy data 🙂

[Anonymous]

Hi @harshadrokade 

We have conversation but no progress, sent you my email in pm if you want to share a sample file.

[Anonymous]

Hi @harshadrokade 

based on your sample data, pm your email address, will send you sample.pbix file

[harshadrokade]

@Anonymous Have sent PM to you mam. Thanks for all your help.

[Anonymous · Accepted solution]

Hi @harshadrokade 

I sent you an email yesterday, didn't you receive it?

[harshadrokade]

Hi @Anonymous I got it mam. It was in spam folder. Thanks a lot for all yuor help. Will check the email now.