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
alexanderg
Advocate II
Advocate II

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

 

Any Reply would be helpful! Thanks in Advance!

 

Regards,

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

21 REPLIES 21
Greg_Deckler
Super User
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)))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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:

DataModel.JPG

 

If I create your Measure:

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

I Get This:

Sin Seleccion.JPG

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:

Con Seleccion.JPG

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

 

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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:

Ultimos 5 Años.JPG

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

 

Regards,

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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:

 

A201427
B201414
C201411
B201578
C201582
D201534
A201654
C201694
D201645
E201613
B201722
D201736
E201717
F201746
A201885
C201820
E201858
G201828

 

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

 

 

 

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

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

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

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

 

Table1Table1.JPG

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

Years Table.JPG

 

 

 

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.

 

Working for 6 years for based on current selection.JPG

 

 

 

 

  

 

 

Hi Anonymous

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

@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

 

 

Dynamic slicer.JPG

 

 correct me if i am doing any wrong in above.

 

 

Anonymous
Not applicable

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

 

 

Value come for that year only.JPG

 

 

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.

 

 

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.