Forum Discussion
Custom years comparison on one chart
Hello,
I have a simple clustered column chart, which is showing month to month comparison between current year and last year.
User can change the Year using slicer, and the report will show always selected year vs last year.
However, I would like to modify it and make both years selectable. So you can have on one chart 2020 vs 2018 or 2019 vs 2016 etc.
Can this be done in PowerBI? Maybe you have some examples?
hi Anonymous
For your case, you need to use DATEADD to custom them,
and this will lead to multiple measure, now you could combine them into one measure and use a slicer to switch them, see more details as below blog:
Regards,
Lin
4 Replies
Anonymous , One way is have trailing /last year meausre like these example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))2nd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
3rd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-3,Year),"12/31"))
4th Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-4,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))
3 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.hi Anonymous
For your case, you need to use DATEADD to custom them,
and this will lead to multiple measure, now you could combine them into one measure and use a slicer to switch them, see more details as below blog:
Regards,
Lin
v-lili6-msft, amitchandak isn't there a solution to make this more dynamic friendly and maintainable. What if a user wants to compare with 8 years ago? Each year comparison you need to put in the measure.
Hi
I have this same problem with thread creator, I want to compare 2 years, but I want to select the years.The link that has give as solution, I don't understand what does it mean. Can anyone help explain to me?
Will be very appreciate the help!
