Get the Conditional Charge Change Period

[Syndicate_Admin]

Hello everyone

I have the following table that has 4 columns called: Period, ID, Job Title, and Start Date. The table can have multiple IDs, and each ID can have multiple charges. I need a new column without using the offset function, which shows me the Period in which I change the Charge for each ID, this new column called "Change Period", does NOT have to consider the first charge for each new Start Date as a change of charge. The idea is that the new column is shown below.

https://docs.google.com/spreadsheets/t/1M0V3SFS4DWJWJT68D7Ko_Directwu/Edit?USB=Drive_Link&White=1099...

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

First, you can create a new index column in the Power Query interface.

Then you can use the following dax to get the result you want.

Change Period = 
VAR CurrentRowID = 'Table'[ID]
VAR PreviousRowID = LOOKUPVALUE('Table'[ID],'Table'[Index],'Table'[Index]-1)
VAR CurrentRowJobTitle = 'Table'[Job Title]
VAR PreviousRowJobTitle = 
    LOOKUPVALUE('Table'[Job Title],'Table'[Index],'Table'[Index]-1)
RETURN
IF(
    CurrentRowID <> PreviousRowID,
    BLANK(),
    IF(CurrentRowJobTitle = PreviousRowJobTitle,BLANK(),'Table'[Period])
)

Best Regards,

Jayleny

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