Help with Showing Current Year and Prior Year with a Filter

[Anonymous]

I am attempting to have reports where I show the measures for Actual and Prior Year Actual. I want users to be able to use a dropdown filter at the top of the report to select the year they want to see. For example, if they select 2021, it will show actual for 2021 compared to the 2020 Act.

I have a very basic table like this. The category is an additional field so the table repeats for category A, B and C.

These are my calculations.

Cases/Hour=sum(Cases)/sum(Hours)
PY Cases/Hour = calculate([Cases/Hour],DATEADD('Table'[Month_Date],-1,YEAR)

I can create a table in the Report Window that looks like this:

If I go into the filters for the actual TABLE (in the filter pane) and filter to Month_Date >=1/1/2021, it shows me this which is what I would expect.

However, if I add a drop-down filter to the top of the report for the year and select 2021, this is the result I get. It drops the PY data for 2020.

How do I create a report where users can filter to a certain year but also see the PY data?

[parry2k]

@Anonymous good to hear. Glad I could help!

That's why all these small details are important and soon I will be starting some training course to cover fundamentals where people struggle so much and with little tweak/knowledge they can have a scalable and much better solution and save lots of time.

Good luck!

[parry2k · Accepted solution]

@Anonymous for time intelligence calculation it is a best practice to add a date dimension, you can easily add by looking at my post and then change your measure to refer to the date column from this new dimension.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k  THANK YOU THANK YOU THANK YOU!!!! I have been struggling with figuring out the PY calculations and how to set everything up in the data model. Your post was helpful. It took me a few tries to figure it out but I think I understand the "why" behind it and the importance of a date table. My tables and graphs are working as I need them to. I appreciate your help!