Display row headers that reflect the year prior to the one selected in the slicer

[PowerWhy]

Hello,

For a table visual please could you tell me how to display row headers for the prior year if I select the current year in a slicer? I've upload the pbix file to https://file.io/BlbmPmmHvb9E 

i.e. if I select 2024 in the slicer I should see the customers from 2023 (i.e. "D" should also be visible).

I know how to change the filter context of the "Amount" to the prior year but I'm stumped how to control the row headers to make customer "D" also appear if I select year 2024. 

Many thanks for any help!

PW

[audreygerred]

Hello! Ideally you will have a star schema with a date dimension table, but you can achieve what you need even without one.

I created a table that has you data, but instead of just the year, I have a date. You will need an actual date for this to work, so if you do not have actual months and days just create a new column in Power Query that creates a date (just pick any date, like December 31 and concatenate with the year so that you have a date field).

Once you have your date field, create a measure for Amount. Total Amount = SUM('YourTable'[Amount])

Next, create the measure for prior year. Total Amount PY = CALCULATE([Total Amount], SAMEPERIODLASTYEAR('YourTable'[Date].[Date]))  <-- if you do have a date dim table and it's marked as date table the syntax would be ('YourTable'[Date]).

Now that you have these measures, in your visual if you put the Total Amount PY measure in the visual and filter to 2024, the 2023 figures will show (including D). You can even show them both so users can easily see where you lost or gained a customer.

[PowerWhy]

Thank you Audrey but for some reason I am still not seeing the customer "D" have I misunderstood something? (pbix file: https://file.io/DXz6tuchkE3C )

[audreygerred]

Hello - I do not see the file you mentioned - it says deleted. 

[PowerWhy]

Apologies please can you try https://file.io/8ZldlcJuHFPT ?

[audreygerred]

Hello! I found the issue. In my earlier response for PY I provided this:

Total Amount PY = CALCULATE([Total Amount], SAMEPERIODLASTYEAR('YourTable'[Date].[Date]))  <-- if you do have a date dim table and it's marked as date table the syntax would be ('YourTable'[Date]).

In your file, you used the syntax for your previous year calculation as for when you have a date dimension table, however, since you don't have a date dim table, you need to use the syntax I provided for that. I updated your measure from:

Amount PY = CALCULATE([Total Amount], SAMEPERIODLASTYEAR('Data'[Date]))

to

Amount PY = CALCULATE([Total Amount], SAMEPERIODLASTYEAR('Data'[Date].[Date]))

I also put year in the slicer instead of the whole date. 

[PowerWhy · Accepted solution]

Brilliant thank you Audrey! 🙂 Sorry why did the slicer need the year from the date hierachy rather than just the date for the "D" row header to show?

[audreygerred]

Hi! It doesn't work with date - it needs the hierarchy.