cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## DAX: Measure to Separate This Year from Last Year

Hi!

I have created a colum that finds tha MAX value from a table and the output would be "TY" as in"This Year".

Then, the MAX - 1 would be "LY" as in "Last Year".

TY_LY = IF(DB[Year]=MAX(DB[Year]);"TY";IF(VALUE(DB[Year])=VALUE(MAX(DB[Year]))-1;"LY";"2LY"))

Since it´s a column and not a measure, when I unfilter year 2020, it stills shows me year 2019 as "LY".

¿How can I create a measure that when I filter only year 2019 and 2018, the result would be TY and LY respectively?

Regards

1 ACCEPTED SOLUTION
Community Support

You may refer to the measure below.

Measure =
SWITCH (
SELECTEDVALUE ( DB[Year] )
- MAXX (
ALLSELECTED ( DB[Year] ),
DB[Year]
),
0, "TY",
-1, "LY",
-2, "2LY"
)

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

You may refer to the measure below.

Measure =
SWITCH (
SELECTEDVALUE ( DB[Year] )
- MAXX (
ALLSELECTED ( DB[Year] ),
DB[Year]
),
0, "TY",
-1, "LY",
-2, "2LY"
)

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors