Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

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

 

wegemmell_1-1695914358487.png

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
        )
    ))

 

wegemmell_1-1695918002726.png

 

 

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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
        )
    ))

 

wegemmell_1-1695918002726.png

 

 

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.