Compare column value with previous row

[krtalvis]

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.

Thanks in advance

[Prabaharan1]

Can you please share the my request, output power bi

Thanks in Advance!

[v-jiascu-msft · Accepted solution]

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

[Prabaharan]

Creation required to power bi:
When I try to compare a value in a column to the row before it, I'm finding it a little difficult with DAX. I'm having trouble finding a satisfactory solution at this time. I chose to come here for assistance because I had been attempting to figure it out for more than a week. Thanks in advance!

Counting switches from Training for each employee's name, date, and financial quarter is what I'm attempting to accomplish. (Dont consider switches counting in Leave and Absent.) We have multiple training, this is for sample data.
As a result, the data would resemble this:
Note: Source file for Columns A through C; output for Columns D and E is required.

EMP_NameDateTrainingSwitch Count by MonthSwitch Count by Quarter

Prabaharan	8-Jan-23	Excel	0	0
Prabaharan	14-Jan-23	SQL	1	1
Prabaharan	18-Jan-23	Power bi	1	1
Prabaharan	18-Jan-23	Leave	0	0
Prabaharan	20-Jan-23	Leave	0	0
Prabaharan	25-Jan-23	Excel	1	1
Prabaharan	8-Feb-23	Excel	0	0
Prabaharan	14-Feb-23	SQL	1	1
Prabaharan	18-Feb-23	Power bi	1	1
Prabaharan	18-Feb-23	Leave	0	0
Prabaharan	20-Feb-23	Absent	0	0
Prabaharan	25-Feb-23	Power bi	0	0
Prabaharan	8-Mar-23	Excel	0	1
Prabaharan	14-Mar-23	Excel	0	0
Prabaharan	18-Mar-23	Excel	0	0
Prabaharan	18-Mar-23	Excel	0	0
Prabaharan	20-Mar-23	Excel	0	0
Prabaharan	25-Mar-23	Excel	0	0

Screenshot: 

[SherryZhao]

hi Dale, I want to know how could i get the row of "success" in Dax? Thanks in advance.

[krtalvis]

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