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
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,
Solved! Go to 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.
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)))
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
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. 🙂
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,
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.
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
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?
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?
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.
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
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
If 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.
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.
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.
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.
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
111 | |
98 | |
98 | |
92 |