cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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?

1 ACCEPTED SOLUTION
Community Support
Community Support

hi  @mat_k 

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:

https://community.powerbi.com/t5/Community-Blog/Dynamically-change-the-information-within-a-visual-v...

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

hi  @mat_k 

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:

https://community.powerbi.com/t5/Community-Blog/Dynamically-change-the-information-within-a-visual-v...

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors