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

[venug20]

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

[v-danhe-msft · Accepted solution]

Hi @venug20,

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

Create a new table:

Table = DISTINCT('Calendar'[Year])

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:

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

Regards,

Daniel He

[Sania]

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. 

[iambarr]

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

[Anjali_Prakash]

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.

[SavanahGrant]

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]

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

[Ashish_Mathur]

Hi,

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

[venug20]

@v-danhe-msft

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

[Ashish_Mathur]

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.

[venug20]

@Ashish_Mathur

Thank you for your effort. 

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

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

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

[Ashish_Mathur]

Hi,

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

Hope this helps.

[venug20]

@Ashish_Mathur

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

Thanks in advance...

[Ashish_Mathur]

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.