HOW TO GET LAST YEAR DATA IF FILTER CURRENT YEAR

[RanHo]

Good Day!

   I have a problem wherein I need to get the last year code if I will use or filter the current year, this are my sample data:

		USER TABLE
ID	USER NAME	TRANSACTION CODE	TRANSACTION DATE
1001	Paul	AC-12389	5/13/2023
1002	Dennis	AC-12980	5/27/2023
1003	Gregory	AC-12982	6/2/2023
1004	Nathan	AC-12991	6/18/2023
1005	Scott	AC-13001	6/27/2023
1006	Dennis	AC-13058	5/27/2024
1007	Gregory	AC-13272	6/2/2024
1008	Paul	AC-13599	5/13/2024
1009	Scott	AC-13601	6/27/2024
1010	Nathan	AC-13668	6/18/2024

as you can see I have users with transaction code with diff year, I need to create column or measure to display last year transaction code when I'm using current year as a filter.

Sample result: in this table let just say I will use filter = TRANSACTION YEAR =  2024
then it will show transaction code for 2023, also need to compare 2023 and 2024 transaction code side by side. Is it possible?

		TABLE
NAME	ID	2023 TRANSACTION CODE	2024 TRANSACTION CODE
Paul	1001	AC-12389	AC-13599
Dennis	1002	AC-12980	AC-13058
Gregory	1003	AC-12982	AC-13272
Nathan	1004	AC-12991	AC-13668
Scott	1005	AC-13001	AC-13601

Thanks in advance!
~Ran

[Ritaf1983 · Accepted solution]

Hi @RanHo 
You can create a date table :

and 2 measures :

This year code = max ('Table'[TRANSACTION CODE ])

PY code = CALCULATE([This year code], PREVIOUSYEAR('Date'[Date]))

Result :

Note that you have 2 id's for every name ...

I aggregated them to the first name, but you can solve it in other ways according to the needed logic

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[RanHo]

@Ritaf1983 Thanks a lot! Your solution worked.

[RanHo]

@Ritaf1983  I'll try this and get back to you.

[RanHo]

@bhanu_gautam I'll try this and get back to you.

[bhanu_gautam]

@RanHo  , First extract year from transaction date Create a calculated column

    Year = YEAR('USER TABLE'[TRANSACTION DATE])
 
Then create a calculated column to get the transaction code from the previous year.
Previous Year Transaction Code =
VAR CurrentYear = 'USER TABLE'[Year]
VAR UserID = 'USER TABLE'[ID]
RETURN
CALCULATE(
MAX('USER TABLE'[TRANSACTION CODE]),
FILTER(
'USER TABLE',
'USER TABLE'[ID] = UserID &&
'USER TABLE'[Year] = CurrentYear - 1
)
)
 
create a table visual in Power BI and add the USER NAME, ID, Previous Year Transaction Code, and TRANSACTION CODE columns. Apply a filter on the Year column to show only the current year

[RanHo]

@bhanu_gautam I already tried your solution but I'm getting no data, no data display on table maybe I'll try to recheck. But thanks for giving solution, appreciate it.