- 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

Calculate Year over Year comparison dynamically based on the [year] slicer
Hello All,
I have a question regarding dynamically calculate the year over year comparison.
Suppose I have a table that look like this:
Sales: | ||||
2019 | 2020 | 2021 | 2022 | |
NY | 400K | 385K | 800K | 750K |
NJ | 200K | 300K | 250K | 400K |
CT | 150K | 400K | 280K | 300K |
and there is a slice where I can select what is the current year.
if I would like to construct a table that dynamically displays my current year selection and automatically populates the sales for the previsouly year and the sales increase (decrease), for example:
[Year] selected 2021
Current Year | Previous Year | Increase (Decrease) | |
NY | 800K | 385K | 415K |
NJ | 250K | 300K | (50K) |
CT | 280K | 400K | (120K) |
How can I achieve this?
Thank you all for your help!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Anonymous ,
There's a lot of ways to do this, here's one way:
1) To use time intelligence measures in DAX you need a date table. I created a basic one and marked as the date table using this code:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,
You may download my PBI file from here.
Hope this helps.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

hi @tom480 ,
Thank you so much for the response. I am fairly new to PBI community and did not understand everything you did to make the magic happen, but I enjoy learning pbi and am looking forward to learning pbi from you in the future.
Thank again.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

hi @Anonymous - if you need help with anything, always here to help you! I enjoy solving puzzles. Have a great weekend! Tom 😀
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Anonymous ,
There's a lot of ways to do this, here's one way:
1) To use time intelligence measures in DAX you need a date table. I created a basic one and marked as the date table using this code:

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
04-18-2024 12:03 PM | |||
08-27-2024 06:22 AM | |||
06-03-2024 10:20 PM | |||
12-02-2023 11:47 AM | |||
10-08-2024 05:39 AM |
User | Count |
---|---|
87 | |
81 | |
53 | |
38 | |
35 |