cancel
Showing results for
Did you mean:
New Member

## LookUP the person's UNIT if he exists in the last year

Hello everyone,

I'm trying to find the previous year's (YEAR-1) UNIT for each PERSON ID in each row.
Can someone help me with this? How can I do this using a MEASURE?  I'm so new to power BI, struggling with various approaches.

ACTUAL DATA:

PERSON ID | DATE | UNIT

123 | 01-01-2021 | SBS-EM

123 | 01-01-2020 | SBS-EM

123 | 01-01-2018 | SBS-EM

123 | 01-01-2017 | SBS-EM

789 | 01-01-2021 | XYZ

789 | 01-01-2020 | XYZ

789 | 01-01-2019 | XYZ

789 | 01-01-2018 | XYZ

789 | 01-01-2017 | XYZ

DESIRED OUTPUT (PREVIOUS YEAR UNIT column):

PERSON ID | DATE | UNIT | PREVIOUS YEAR UNIT

123 | 01-01-2021 | SBS-EM | SBS-EM

123 | 01-01-2020 | SBS-EM |

123 | 01-01-2018 | SBS-EM | SBS-EM

123 | 01-01-2017 | SBS-EM |

789 | 01-01-2021 | XYZ | XYZ

789 | 01-01-2020 | XYZ | XYZ

789 | 01-01-2019 | XYZ | XYZ

789 | 01-01-2018 | XYZ | XYZ

789 | 01-01-2017 | XYZ |

PFB the screenshot of the same.

Thanks a ton for your help

Regards
Santosh
1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

I think LOOKUPVALUE should work.

As a calculated column, it would look like this:

``````PY Unit =
LOOKUPVALUE (
Data[Unit],
Data[Person ID], Data[Person ID],
Data[Date], DATE ( YEAR ( Data[Date] ) - 1, 1, 1 )
)``````

There are plenty of other ways to do this though. I might do this if I were writing a measure:

``````PY Unit =
CALCULATE (
SELECTEDVALUE ( Data[Unit] ),
ALLEXCEPT ( Data, Data[Person ID] ),
DATEADD ( Data[Date], -1, YEAR )
)``````
New Member

Thank you for the quick response and solution.

It almost worked, but even when there is no data in the previous year, this measure is giving the unit.

For example for the person with ID 123 for DATE 01-01-2020,even when there is no record in 2019, the measure is giving PY UNIT as SBS-EM

Super User

Odd. I'm not seeing that in my testing.

If you have a date dimension (calendar) table, you should be able to simplify it a bit.

``````PY Unit =
CALCULATE (
SELECTEDVALUE ( Data[Unit] ),
DATEADD ( dimDate[Date], -1, YEAR )
)``````

Announcements