cancel
Showing results for
Did you mean:
Regular Visitor

## 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 @ianhyj ,

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
Regular Visitor

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 @ianhyj - if you need help with anything, always here to help you! I enjoy solving puzzles. Have a great weekend!  Tom 😀

Resolver I

Hi @ianhyj ,

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

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!