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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
venug20
Resolver I
Resolver I

I want to display last 5 years sales when i select one year

Hi every one,

 

i have a table. i want to show data like "Order date (Years)", "Sales".  i have given one slicer for years, when i select year from slicer. i want to display last 5 years slaes.

 

How can i achive this.... Please help on this...

 

Thanks in advance.....

 

 

 

1 ACCEPTED SOLUTION
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @venug20,

Based on my test, you could refer to below steps:

Create a new table:

Table = DISTINCT('Calendar'[Year])

1.PNG

Create a measure:

Measure = CALCULATE(SUM(Orders[Sales]),FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))

Use the new table's [Year] column as slicer and you could get the correct result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @venug20,

Based on my test, you could refer to below steps:

Create a new table:

Table = DISTINCT('Calendar'[Year])

1.PNG

Create a measure:

Measure = CALCULATE(SUM(Orders[Sales]),FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))

Use the new table's [Year] column as slicer and you could get the correct result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Danhe,

I have almost similar requirement but its based on the date. Say I select todays date 22 Feb 2023 in slicer then I should get last 5 years data (including quarters,months,dates) based on selected date. 

This was a life-saver for me! Thanks for the solution.

Hi Daniel,

 

This works fine for me but I have the year slicer that controls all the visuals on the page. So if I use the Table[year] column as my slicer, then the other visuals remain unchanged giving the total for all the years. Please suggest how can I have the Year slicer from the Calendar table which controls all the other visuals for the selected year at the same time displaying Sales for the last 4 years.

 

Hi @Anjali_Prakash 
I had a similar issue and fixed it by removing the filter (REMOVEFILTERS) on the main calendar table. Using the sample code in this thread it would look like this:

 

Measure = CALCULATE(SUM(Orders[Sales]),REMOVEFITERS('Calendar'[Year]),(FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))

Hope this helps! 

Anonymous
Not applicable

Hi 

 

I could not get the results like i want to. 

i need to display the current year selected and previous year by only selecting on one year.

Which means, i need the data of 2016 and 2017 to be displayed when i select on 2017. Ive tried using this formula below but its not working. Is there any other way? My column consumes of formula which leads to another formula but at the end i would only need to use the total column data to be selected based on year.

Measure = CALCULATE(SUM(Orders[Sales]),FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))

 

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@v-danhe-msft

 

Thanks for your solution, great work. which i has been expected, got the same solution from you.

Ashish_Mathur
Super User
Super User

Hi,

 

You may please follow these guidelines

 

  1. Load this data in PowerBI desktop and name it as Data
  2. Create a Calendar Table by going to Modelling > New Table and writing this formula: Calendar=Calendar(MIN(Data[Order Date]),MAX(Data[Order Date]))
  3. Create a relationship from the Order Date column of the Data Table to the Order Date column of the Calendar Table
  4. In the Calendar Table, write these calclated column formulas: Year=year(calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
  5. In your visual, drag the Year from the Calendar Table and select any one year
  6. Write these messures

 

Total sales = SUM(Data[Sales])

Total sales for last 5 years = CALCULATE([Total sales],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-4),MAX(Calendar[Date])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

Thank you for your effort. 

 

i have done which is giving steps in above. i got output like below.....

 

Last5Years-Sales-1.jpg

 

If not happend selection from slicer. output come like this...

 

Last5Years-Sales.jpg

 

 

when i select "2017" from slicer. the output should come like "2017, 2016, 2015, 2014, 2013" with respective years sales values.

should not show total sales values to respective years..

 

 

 

Hi,

 

A simple solution is to use a relative date filter.  You may download my PBI file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

There is no "Year"(Slicer) selection option. If possible, Please help on this issue...

 

Thanks in advance...

 

Hi,

 

In the PBI file that i shared with you, i have already shown the working of the relative date filter in the in the Visual filter section of the FIlter pane.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.