Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
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 co...
  • 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.

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.