Forum Discussion

Frequent Visitor

8 years ago

Solved

Compare column value with previous row

Hi, Im having a bit of struggle with DAX when trying to compare a value in a column to the previous row. So far, there doesnt seem to be a good answer that works for me. I have been trying to fig...

v-jiascu-msft

8 years ago

Hi krtalvis,

Try this formula please. You can check out the demo in the attachment.

Measure 2 =
SUMX (
    SUMMARIZE (
        '180720',
        '180720'[time],
        '180720'[City],
        '180720'[name],
        '180720'[success],
        "switch",
        VAR lastTime =
            CALCULATE (
                MAX ( '180720'[time] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] < EARLIER ( '180720'[time] ) )
            )
        VAR lastSuccess =
            CALCULATE (
                MIN ( '180720'[success] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] = lastTime )
            )
        RETURN
            IF ( [success] = lastSuccess || ISBLANK ( lastSuccess ) = TRUE (), 0, 1 )
    ),
    [switch]
)

Best Regards,

Dale

Compare column value with previous row.pbix71 KB

v-jiascu-msft

Microsoft Employee

8 years ago

Hi krtalvis,

Try this formula please. You can check out the demo in the attachment.

Measure 2 =
SUMX (
    SUMMARIZE (
        '180720',
        '180720'[time],
        '180720'[City],
        '180720'[name],
        '180720'[success],
        "switch",
        VAR lastTime =
            CALCULATE (
                MAX ( '180720'[time] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] < EARLIER ( '180720'[time] ) )
            )
        VAR lastSuccess =
            CALCULATE (
                MIN ( '180720'[success] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] = lastTime )
            )
        RETURN
            IF ( [success] = lastSuccess || ISBLANK ( lastSuccess ) = TRUE (), 0, 1 )
    ),
    [switch]
)

Best Regards,

Dale

Compare column value with previous row.pbix71 KB