Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

Get the Conditional Charge Change Period

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=109973665563671784901&Ambltbob=True&St=True

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.