cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 figure it out for a whole day now which is why i have decided to turn here for help.

What I am trying to do is to count switches from True-to-False and False-to-True for each different name. I need this to understand which names are the most flaky.
So the data would look something like this:

time                                   City                name                            success
4/19/2018 hh:mm:ss          LA                  Jill                                 True
4/19/2018 hh:mm:ss          CA                  Miranda                       True
4/19/2018 hh:mm:ss          CA                  Miranda                       False
4/19/2018 hh:mm:ss          CA                  Miranda                       False
4/20/2018 hh:mm:ss          LA                  Jill                                 True
4/20/2018 hh:mm:ss          LA                  Jill                                 False
4/20/2018 hh:mm:ss          LA                  Miranda                        True
4/20/2018 hh:mm:ss          LA                  Jill                                 False
4/21/2018 hh:mm:ss          LA                  Jill                                 True
4/21/2018 hh:mm:ss          CA                  Miranda                        False

So i guess there would be two ways - either to create a new columns that marks 0/False or 1/True whether there has been a change, or to just return the overall number for each name. For this example, the return should be something like this:

Name              Switches
Jill                    2
Miranda           3

Another thing is that both names can be in different cities, so it should be filterable by city, meaning if i take one city out, the switch would also not take into count the rows that have been filtered out. In this example the name Miranda would have -1 switch if i would take out LA.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Compare column value with previous row

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super Contributor

## Re: Compare column value with previous row

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Compare column value with previous row

Had to do some few modifications to suit me even more, but this actually worked. Thanks !

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 50 members 1,222 guests
Recent signins: