cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

1 ACCEPTED SOLUTION
Employee

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:

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.
13 REPLIES 13
Employee

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:

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

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.

Frequent Visitor

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

Regular Visitor

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.

New Member

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

Super User

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
Resolver I

@v-danhe-msft

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

Super User

Hi,

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
Resolver I

@Ashish_Mathur

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

Super User

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
Resolver I

@Ashish_Mathur

Super User

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