cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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!

1 ACCEPTED SOLUTION
Resolver I

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:

TableDT =
VAR MinYear = YEAR ( MIN ( Table1[Date] ) )
VAR MaxYear = YEAR ( MAX ( Table1[Date] ) )
RETURN
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)

2)  Next I created three measures:

2a)
Current Year = SUM(Table1[Value])

2b)
Last Year = CALCULATE(
[Current Year],
SAMEPERIODLASTYEAR(Table1[Date])
)

2c)

Chg Year = [Current Year] - [Last Year]

That gives me these results:

Matrix visual with year slicer

Simple Date Table for example

Here's the PBIX file if you want to review in your desktop application.

4 REPLIES 4
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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.

Resolver I

hi @Anonymous - if you need help with anything, always here to help you! I enjoy solving puzzles. Have a great weekend!  Tom 😀

Resolver I

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:

TableDT =
VAR MinYear = YEAR ( MIN ( Table1[Date] ) )
VAR MaxYear = YEAR ( MAX ( Table1[Date] ) )
RETURN
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)

2)  Next I created three measures:

2a)
Current Year = SUM(Table1[Value])

2b)
Last Year = CALCULATE(
[Current Year],
SAMEPERIODLASTYEAR(Table1[Date])
)

2c)

Chg Year = [Current Year] - [Last Year]

That gives me these results:

Matrix visual with year slicer

Simple Date Table for example

Here's the PBIX file if you want to review in your desktop application.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors