Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
harshadrokade
Post Partisan
Post Partisan

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

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.
1 ACCEPTED SOLUTION

Hi @harshadrokade 

 

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

View solution in original post

31 REPLIES 31
v-kkf-msft
Community Support
Community Support

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

@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.
 

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

v-kkf-msft
Community Support
Community Support

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"

 image.png

 

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

 

vkkfmsft_0-1624441561041.png

image.png

 

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.

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.

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.

 

image.png

 

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.

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..

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

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- 

 

YearSalesNo of companies
201540050

2016

60030

2017

100050

2018

89090
2019234130
20202344200
202134480
2022456170

 

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)

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

@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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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..

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

aj1973
Community Champion
Community Champion

Hi @harshadrokade 

Do you want to try this!

aj1973_0-1624225045116.png

 

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

@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.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

@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 🙂 

@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.

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.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Thanks sir. Will try it out,

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors