cancel
Showing results 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

## Show the last X years based on the current Selection of the Year

Hi Everybody

How can I write a DAX expression that show only the Sales of the last 5 five years based on the current selection of the year.
For Example:
-If I select 2015, the chart should show only sales for the years 2015, 2014, 2013, 2012, 2011
-If I select 2012, the chart should show only sales for the years 2012, 2011 2010, 2009, 2008

Regards,

1 ACCEPTED SOLUTION
Super User

OK, thought of a nifty way of doing this. I call this the "Unrelated Slicer" solution. Create your Year table as I said before but DON'T create the relationship between the Year table and your Sales table. In your Sales table, create a Measure like this:

`Measure = IF(MAX(SalesLast5[Year])>(MAX(Years[Year])-5) && MAX(SalesLast5[Year])<=MAX(Years[Year]),1,0)`

Create a slicer using your Year table. Create a table visualization with Year from the Sales table and Sales (value) from the Sales table. Drag your Measure to your filter area for the visual. Filter it to 1 only.

Presto.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
21 REPLIES 21
Super User

Without knowing anything about your data model, I would think it would be something along the lines of:

`CALCULATE(SUM([Sales]),FILTER(SalesTable,[Year]>(MAX([Year])-5)))`

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thanks For your reply, but the expression that you suggest works only as individual indicator and only for the maximum year. Let me explain my situation.
Imagine the following dataset:

Sales Last 5 Years = CALCULATE(SUM([Sales]),FILTER(Tabla1,Tabla1[Year]>(MAX([Year])-5)))

I Get This:

In fact, the measure calculates the sales for the last 5 years, but the chart is still showing sales for every year. The other problem is that if I choose a year in the slicer, the sales only looks for that single year:

I want to see in the chart only the last 5 years according to the current selection for the year. For example If I Select 2014 in the slicer, in my graph I want to see the sales only for 2014, 2013, 2012, 2011, 2010.

Regards

Super User

Well, in fairness, I did say "along the lines of". Easiest way (and you may have to tweak this formula) would be to create a separate table called "Years" and just put the years in it. Relate the two tables on "Year" columns. Then, create a custom column in "Years" table like this:

`SalesLast5 = SUMX(FILTER(ALL(SalesLast5),SalesLast5[Year]>(Years[Year]-5) && SalesLast5[Year]<=Years[Year]),SalesLast5[Sales])`

Eh, maybe not so much along the lines of the first formula but in the same general realm of thought. 🙂

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Sorry @Greg_Deckler Thanks for your reply but I don't understand exactly what you refers in your solution using a new table and a calculate column. At first glance I have the impression that your solution is for get in each year the sales of the last 5 years? Or I am wrong?. maybe If I didn't explain well.
Considering the dataset showing in the previous comments.

What I get is dynamically filter the graphic through the DAX expression, which show only the last 5 years taking the selected year in the slicer as the maximum year. For Example If I select 2013 in my slicer, in the graph I want to see this:

Sales for each year, but only the last 5 is displayed.

Regards,

Super User

OK, thought of a nifty way of doing this. I call this the "Unrelated Slicer" solution. Create your Year table as I said before but DON'T create the relationship between the Year table and your Sales table. In your Sales table, create a Measure like this:

`Measure = IF(MAX(SalesLast5[Year])>(MAX(Years[Year])-5) && MAX(SalesLast5[Year])<=MAX(Years[Year]),1,0)`

Create a slicer using your Year table. Create a table visualization with Year from the Sales table and Sales (value) from the Sales table. Drag your Measure to your filter area for the visual. Filter it to 1 only.

Presto.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
New Member

I am facing the same issue.

i want to show the company names whenever i select year from slicer and also cumulate the sales value.

like for example

if i have the data like this:

 A 2014 27 B 2014 14 C 2014 11 B 2015 78 C 2015 82 D 2015 34 A 2016 54 C 2016 94 D 2016 45 E 2016 13 B 2017 22 D 2017 36 E 2017 17 F 2017 46 A 2018 85 C 2018 20 E 2018 58 G 2018 28

then when i select 2014 in slicer it should show a, b, c companies with their respective sales numbers.

but when i select 2015 it show show the current companies as well previous companies like a, b, c and d with b and c having cumulative values and so on.

we can do this with multiple selection in the slicer but the client doesnt want to do that and instead wants the above solution with just single selection in slicer.

pls help

Post Partisan

HI @Greg_Deckler This has helped me as well. The only issue I am facing is that since the slicer value is selected from other delinked table, the other visuals that I have created from sales table are not getting filetered when I change the date. How can I manage this?

Anonymous
Not applicable

2020

2021

in matrix
2014   2015   2016  2017   2018 2019    2020

product 1     30M    40M    50M   60M   60M  70M     8M

product 2     50M    10M    35M   48M    45M  34M     NA

product 3     34M    76M    43M    54M   23M   34M   76M

*In my dataset for 2020 I have 6 years of data(2014-2019). My requirement when I click on 2020 (year slicer) I have to show (2015-2019)i.e, latest 5 years of data.

*For 2021 also I have 6 years of data(2015-2020), when I click on 2021 (year slicer) I have to show (2016-2020)i.e,latest 5 years of data.

*But in my matrix table values of Product1 ,Product2, Product3 are measures. Then, still your DAX code will work?

Anonymous
Not applicable

Year slicer

2020

2021

in matrix
2014    2015     2016     2017    2018    2019    2020

product 1        30M     40M     50M       60M     60M    70M     8M

product 2         50M    10M      35M      48M     45M    34M     NA

product 3         34M    76M      43M    54M       23M    34M     76M

*In my dataset for 2020 I have 6 years of data(2014-2019). My requirement when I click on 2020 (year slicer) I have to show (2015-2019)i.e, latest 5 years of data.

*For 2021 also I have 6 years of data(2015-2020), when I click on 2021 (year slicer) I have to show (2016-2020)i.e,latest 5 years of data.

*But in my matrix table values of Product1 ,Product2, Product3 are measures. Then, still your DAX code will work?.

Thank You

New Member

I also use matrix chart and this DAX code don't work. Help me.

Regular Visitor

Thanks for your DAX formula. I have managed to get it working on my data.

But, I have two requirements if possible please:

First: I need to make it show me the exact selected years whenever more than one year is selected.

For example:

When I select 2020 year only, it will show me the 2020 year column along with 4 more years columns in the past,

But, if I selected 2 years, let's say 2018 and 2020 then it will show me the column of these two years only without 2019.

Second: Whenever I select only one year, I need also to get the data of last year next to the data of current year. with the formula above I get the last year data as blank.

Many thanks

Thanks @Greg_Deckler It Works!

Regards,

Anonymous
Not applicable

Hello @alexanderg

I'm Having Same Issue like You

I also want result based on Current Year Selection from slicer to Line chart. when i click on Particular year it will show me selected year and Previous 6 years.For that also gone through the this post and Prepared following

Based on any year selection from slicer it will show that as well as Previous 6 yearsIf you can check when i click on year 2011 from slicer it will show me value 1 for all Previous years i.e 2011,2010,2009,2008,2007

But I want same is Line chart when i click on any year from slicer its show me that year as well as previous 6 years Sales. Help me in that Really Appricates

I am having below tables :

1st

Table1

in that i have Prepared below measure

Measure = IF(MAX(Table1[Year])>(MAX(Years[Year])-5) && MAX(Table1[Year])<=MAX(Years[Year]),1,0)

Sales Last 5 Years = CALCULATE(SUM([Sales]),FILTER(Table1,Table1[Year]>(MAX([Year])-5)))

and 2nd Table is Years Table

When I click in slicer i want result in Line chart as selected Year + Previous 6 Year Sales Values.

Please correct me if am doing any thing wrong in Above.

Anonymous
Not applicable

Now working fine, I gone through post again and done some changes in formulas and its working fine for Line chart as well.

When I selected Year 2011 from slicer it shows me that year as well as Previous 6 years Sales under Line chart

Thanks @alexanderg and @Greg_Deckler Really nice Post its help me and poeple who are new in Power BI and in DAX.

New Member

Hi Anonymous

can you share your filnal dax plz. I have the same problem

Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Is there a way to evaluate a measure based on slicer selctions? For example I have a Year over Year card that i want to be evaluated based on the two years selected, so if someone clicks on "2018 and 2017" then it does those two years, but if i select "2017 and 2016" then it does those two years.

Anonymous
Not applicable

Hello

Same Requirtment i was trying for my main database where i need to Implement this

I have prepared years tables separately and trying to genrate Same Scenario But when i clicked and selected any year from slicer

not displaying Current +Previous 6 years data. please check below image

When i clicked on date 18 April 2016 from Slicer  its only show value 1 against for that year only But

I have prepared below formulas for this database

Measure = IF(MAX('Global'[weekno])>(MAX(WeekNo[weekno])-6) && MAX('Global'[weekno])<=MAX(WeekNo[weekno]),1,0)

This Week for last 6 Years = CALCULATE(SUM('Global'[ThisWeek]),FILTER('Global','Global'[weekno]>(MAX([weekno])-6)))

here in years tables i have data like

e.g 02-03-2017 in this format

correct me if i am doing any wrong in above.

Anonymous
Not applicable

@Greg_Deckler

when i selected date from slicer 11 April 2016 its show me  value 1 in Measure column  against this date only.

I used below formulas for that

Measure = IF(MAX('Global'[weekno])>(MAX(WeekNo[weekno])-6) && MAX('Global'[weekno])<=MAX(WeekNo[weekno]),1,0)

This Week for last 6 Years = CALCULATE(SUM([ThisWeek]),FILTER('Global','Global'[weekno]>(MAX([weekno])-6)))

But i want Result like below when i clicked on date 11 April 2016 its show me values for Previous date like

4 April 2016, 28 March 2016, 21 March 2016, 14 March 2016, 7 March 2017, 29 February 2016 for Previous 6 years .

Correct me if i doing wrong in formulas.

Super User

Oh, I understand now. Unfortunately, I don't think that you can get there with a slicer. The reason is that the minute you use a slicer, it is going to filter your tables and other visualizations down to whatever you selected in the slicer. I'll try to ponder a way around that, perhaps a matrix of values like:

Year,Year1,Value

2009,2009,743

2009,2008,743

2009,2008,743

2009,2007,743

2009,2006,743

Then you would use Year in your slicer, Year 1 in your table and MAX/MIN of Value in your table.

That's a very strange request. What I gave you earlier will give you the SUM for the last 5 years.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors