Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate the difference between each previous value in a column for matching items only

Good day, Community.

I am working on a project where I need to calculate the difference in [Cycles] for like [Registration] values for each previous instance of an event in a table, where the order of events is based on the [PRIMARYKEY] of the table. The first instance of each can be left blank. 

 

I have worked most of this out in a calculated column, but keep running into the issue of using like registration values only. It seemed simple to me at first, but I am missing something. Any help is greatly appreicated. Below is a link to some sample data. I have included a screenshot as well.

 

EXAMPLE DATA

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Disregard... Just got it to work. Thank you for looking, though! Leaving this hear in case anyone else could use.

     

     

    Cycles Since Previous = 
VAR CurrentReg = 'Table'[Registration]
VAR CurrentKEY = 'Table'[PRIMARYKEY]
VAR PrevKEY = CALCULATE(
    MAX('Table'[PRIMARYKEY]),
    FILTER(
        'Table',
        'Table'[PRIMARYKEY] < CurrentKEY &&
        'Table'[Registration] = CurrentReg
    )
)
RETURN
IF(
    ISBLANK(PrevKEY),
    BLANK(),
    'Table'[ Cycles ] - 
    CALCULATE(
        MAX('Table'[ Cycles ]),
        FILTER(
            'Table',
            'Table'[PRIMARYKEY] = PrevKEY
        )
    ))

     

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Disregard... Just got it to work. Thank you for looking, though! Leaving this hear in case anyone else could use.

     

     

    Cycles Since Previous = 
VAR CurrentReg = 'Table'[Registration]
VAR CurrentKEY = 'Table'[PRIMARYKEY]
VAR PrevKEY = CALCULATE(
    MAX('Table'[PRIMARYKEY]),
    FILTER(
        'Table',
        'Table'[PRIMARYKEY] < CurrentKEY &&
        'Table'[Registration] = CurrentReg
    )
)
RETURN
IF(
    ISBLANK(PrevKEY),
    BLANK(),
    'Table'[ Cycles ] - 
    CALCULATE(
        MAX('Table'[ Cycles ]),
        FILTER(
            'Table',
            'Table'[PRIMARYKEY] = PrevKEY
        )
    ))