topic Re: LookUP the person's UNIT if he exists in the last year in Developer

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

Anonymous — Sun, 23 Jan 2022 05:27:00 GMT

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

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

AlexisOlson — Sat, 22 Jan 2022 23:46:04 GMT

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

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

Anonymous — Sun, 23 Jan 2022 08:19:25 GMT

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

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

Ashish_Mathur — Sun, 23 Jan 2022 23:28:44 GMT

Hi,

You may download my PBI file from here.

Hope this helps.

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

AlexisOlson — Mon, 24 Jan 2022 15:47:34 GMT

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.