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

[Santosh7162]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AlexisOlson]

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 )
)

[Santosh7162]

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

[AlexisOlson]

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 )
)

You can also use PARALLELPERIOD or SAMEPERIODLASTYEAR instead of DATEADD.