Hover showing two years as visual

[PXJ99]

Hi all,

I really need your help please.

I have a table visual which has data in columns from 3 data sets, which is connected to year slicer.

I have been asked to add a hover with bar chart visual showing 2 years. I was able to create a bar chart with current year but I am unable to do one with previous year.

Could you please help, I've added my files to the below link. Would greatly appreacite your help. Thanks.

https://www.dropbox.com/t/DbPbX8SqNJgtDcKC

[PXJ]

Both the solutions provided by Allison and WangBT are brilliant and I have learned interesting DAX measures. Thank you both very much. I'd like to accept both your responses as solutions. 

[Walter_W2022]

@PXJ99 , I may provide another solution, not sure whether it is what you want. 😀

20230331-Test power bi.pbix

[PXJ99]

Thank you very much.

[AllisonKennedy]

@PXJ99 

This is much easier with a proper date table. I have converted your Month and Year columns into a start of month column. Would rather do this in data source or Power Query, but used DAX so I didn't need to connect to your excel files.

I then added my date table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

Finally, created a measure for Prior Year:

Actuals Prior Year =

CALCULATE(SUM(Actuals[Actuals]), DATEADD(Dates_DIM[Date], -1, YEAR))

Other option is to turn off the 'Keep all filters' on your tooltip page and then you can still access all the years. I put 'Year' in the 'small multiples' for Tooltip 2 page:

[PXJ99]

Thank you very much.