- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Display row headers that reflect the year prior to the one selected in the slicer
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Proud to be a Super User! | |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello - I do not see the file you mentioned - it says deleted.
Proud to be a Super User! | |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:
Proud to be a Super User! | |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi! It doesn't work with date - it needs the hierarchy.
Proud to be a Super User! | |

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
12-16-2023 06:15 AM | |||
07-07-2024 01:03 AM | |||
07-18-2024 04:45 AM | |||
10-16-2024 01:44 AM | |||
10-13-2023 11:15 AM |
User | Count |
---|---|
137 | |
107 | |
85 | |
59 | |
46 |