Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
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.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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 )
)
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |