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

[alexanderg]

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

Any Reply would be helpful! Thanks in Advance!

Regards,

[Greg_Deckler]

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

[alexanderg]

Hi @Greg_Deckler

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:

If I create your Measure:

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

[Greg_Deckler]

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

[alexanderg]

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,

[Greg_Deckler · Accepted solution]

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.

[sanket327]

Hi @Greg_Deckler , @harshadrokade , @alexanderg , @Anonymous 

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

[harshadrokade]

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]

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]

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

[NyHuynh]

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

[osama-selo]

Hi @Greg_Deckler 

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

[alexanderg]

Thanks @Greg_Deckler It Works! 

Regards,

[Anonymous]

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]

 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.

[SarmadMohammed]

Hi Anonymous

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

[Greg_Deckler]

Awesome, always glad to help out! Glad you got it working.

[Drew248]

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]

@Greg_Deckler 

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]

@Greg_Deckler

please check below images

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.

[Greg_Deckler]

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.