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.

[v-kkf-msft]

Hi @harshadrokade ,

I see that you have solved this problem. If possible, could you please share your solution? That will help the other members solve it more quickly.

Best Regards,
Winniz

[harshadrokade]

@v-kkf-msft 

@v-kkf-msft  The solution is as below-

Tables that I have are as below-

• Calendar table with Year columnn
• Sales Table with Year, Sales & No of companies column

Measures created as below-

• 5 yr sales data =

  VAR CurYear = SELECTEDVALUE('Calendar'[Year])

  RETURN

  CALCULATE(SUM(SalesTable[Sales]),FILTER(ALL('Calendar'[Year]),'Calendar'[Year]<= CurYear+2 &&'Calendar'[Year]>=CurYear-2))

• TotalNoofCompanies = SUM(SalesTable[No of companies])

I created a slicer with Year column from Calendar table

I created Table visual with Year & 5 Year Sales data Measure

I created card visual with TotalNoofCompanies Measure

Now when I select any year in slicer, the Table visual shows data of 5 years but the card visual shows No of companies of slicer selected year only.

Thanks to @Vera_33 for all her help.

[harshadrokade]

@v-kkf-msft Sure. As I can;t upload the pbix file, will share resolution in writting in couple of days. 

[v-kkf-msft]

Hi @harshadrokade ,

If you want to change the value of other visuals, I think it is more convenient to create a direct correspondence between the year of the slicer and the year to be filtered. Try the following cade in Power Query:

let
    Source = Table.FromValue(#"Sales Data"[Order Year], [DefaultColumnName = "Year"]),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Filter Year", each {[Year]-2..[Year]+2}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Filter Year")
in
    #"Expanded Custom"

 

Then create * : * relationship between Sales Date table and Slicer table.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[harshadrokade]

Hi @v-kkf-msft, I tried this & this is working fine on the sales data visual however it is not properly filtering other visuals which have yearly data in it. It doesn;t have 5 year data in it. Example I have number of companies shown on another visual of the same dashboard & it provides me 5 year data instead of 1 year when I implement your solution. Pls help with a solution that will provide only slaes data of 5 years but rest of the visuals show data of the year selected in slicer.

[v-kkf-msft]

Hi @harshadrokade ,

If you want to filter other visuals in the same way, you need to create a relationship between the Filter Year column of Slicer table and the year column of Other table, and make the relationship active.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[harshadrokade]

Hi @v-kkf-msft I don;t want otehr visuals to be filtered in same way. I want other visuals to show only 1 year data & that year will be the one which is selected on slicer. So if on slicer I selected 2019, the table visual will show data of 2017, 2018, 2019, 2020, 2021 but the card visual which shows number of companies will show companies of year 2019 only (since this year is selected on slicer) & not all 5 years companies..

[v-kkf-msft]

Hi @harshadrokade ,

I think you need to provide your model. For example, what tables and data you have, the relationships between tables. When one year is selected in the slicer, specify which data and visuals need to be displayed for 5 years and which need to be displayed for 1 year. Then we can find a more suitable way to solve the problem.

Best Regards,
Winniz

[harshadrokade]

Hi @v-kkf-msft 

As I can't share PBIX file due to upload coknstraint, sharing the model details below-

Table name- Company data

Columns- 

Year	Sales	No of companies
2015	400	50
2016	600	30
2017	1000	50
2018	890	90
2019	234	130
2020	2344	200
2021	344	80
2022	456	170

Visuals-

Slicer- Year (Data comes from Year column)

Table- Year & Sales (Only 5 years data that comes from Year & Sales column. If Slicer has year as 2019 selected, this table should show Sales of 2017, 2018, 2019, 2020, 2021)

Card visual- No of companies (Data comes from No of companies. If slicer has year as 2019 selected, this visual should show No of comoanies of 2019 only & not of 5 years like we showing in table)

[harshadrokade]

Hi @v-kkf-msft , Can you pls help me on this request..

[aj1973]

@harshadrokade 

Then create another separate measure for the card where you use SELECTEDVALUE as dax.

Card visual =
VAR _YearSelected = SELECTEDVALUE('Campany data' [Year])

VAR _Result = CALCULATE(SUM('Campany data' [Sales]) , 'Campany data' [Year] = _YearSelected

RETURN

_Result

[harshadrokade]

Thanks @aj1973 . Actually along with card visuals, there are other 4-5 visuals as well on the dashboard (Pie, Line chart, etc.) which shoud show single year (slicer year) data. Creating measures for all of them will be difficult & so I am looking for the measure for 5 year table visual only so that my slicer updates only table data with 5 years whereas my other visuals show slicer year (single year) data only..

[harshadrokade]

Thanks a lot @v-kkf-msft. I will try this out & keep you posted sir. THanks a lot.

[aj1973]

Hi @harshadrokade 

Do you want to try this!

Here is the formula it should be quite easy to understand:

Sales around 5 years =

var _SalesPastYear = CALCULATE(SUM(FactSales[SalesAmount]), DATEADD('Calendar'[Date],-1,YEAR))

var _SalesPast2Years = CALCULATE(SUM(FactSales[SalesAmount]), DATEADD('Calendar'[Date],-2,YEAR))

var _SalesNextYear = CALCULATE(SUM(FactSales[SalesAmount]), DATEADD('Calendar'[Date],+1,YEAR))

var _SalesNext2Years = CALCULATE(SUM(FactSales[SalesAmount]), DATEADD('Calendar'[Date],+2,YEAR))

var _SalesThisYear = SUM(FactSales[SalesAmount])

var Result = _SalesPast2Years + _SalesPastYear + _SalesThisYear + _SalesNextYear + _SalesNext2Years

return

Result

[harshadrokade]

THanks a lot sir @aj1973 . I tried above measure but it is not working. Will you be able to explain this by sharing the PBIX file plssss. 🙂 Thanks a lot

[aj1973]

@harshadrokade 

It all depend on how you use the formula on which model. In my model I didn't use a new table for "Year" didn't need to, so even if I send you my file it wouldn't help you much therefore you will need to adjust our suggestions to your model.

However how about you share your file and it will be much easier for all of us to go quicker and faster to a solution! Maybe a sample PBIX of your model.

[harshadrokade]

@aj1973 Actually Sir, I tried uploading file sir but my organisation doesn;t allow me to uplaod the files in external portal & so I can;t do that sir. Will try again what you suggested & keep you posted & may ask for help again 🙂 

[harshadrokade]

@aj1973 Sir, It will be very helpful if you spare some time & provide a pbix file with me by implementing the above solution. My organisation doesn;t allow me to upload files on external server. I would have uploaded file from my personal computer but it doesn;t have power bi installed on it as it can be installed only with company email id.

[aj1973]

Hi @harshadrokade 

I told you i applied my formula on a very simple model : Calendar Tabel related to Sales Table, nothing complicated. So if your model is as close to mine then apply my formula and it should work. 

Now if you want to share your file then make a sample pbix file of your model and upload it to any cloud service like Google drive, Dropbox....and share with us the URL.

[harshadrokade]

Thanks sir. Will try it out,