Excel count changes based on criteria and date

[sabirguiri]

Hi all,

Could anyone help me with the following?

I am looking for a formula to be able to count changes based on a criteria and date.

For example: ID is A01 and this person is located at Loc1 since 28/04/2022, but on 03/05/2022 it was moved to Loc6 and the calculation needs to show 1.

I would really appreciate it if anyone could help me solve this puzzle in Excel and in Power BI using DAX.

ID	Location	Date	Calculation
A01	Loc1	28/04/2022
A01	Loc1	29/04/2022
A01	Loc1	30/04/2022
A01	Loc1	01/05/2022
A01	Loc1	02/05/2022
A01	Loc6	03/05/2022	1
A01	Loc6	04/05/2022
B02	Loc4	28/04/2022
B02	Loc4	29/04/2022
B02	Loc4	30/04/2022
B02	Loc2	01/05/2022	1
B02	Loc2	02/05/2022
B02	Loc2	03/05/2022
B02	Loc2	04/05/2022

Thank you!

[#2]

@sabirguiri 

Here is a simple Excel formula solution.

Assuming that ID is in cell A1, enter the following formula in D2:

=IF(AND(A2=A1,B2<>B1),1,"")

Fill or copy down.

[#3]

@Hans Vogelaar 

Hi Hans, this works perfectly... thank you so much for solving this so quickly. I appreciate it!
Any suggestions on how to do in Power BI DAX?

[#4]

@sabirguiri 

I'll have to leave that for someone else, sorry.

[#5 · Accepted solution]

@sabirguiri 

That could be

=
VAR currentID = Table1[ID]
VAR currentLocation = Table1[Location]
VAR currDate = Table1[Date]
VAR previousDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLSELECTED ( Table1 ),
        Table1[ID] = currentID,
        Table1[Date] < currDate
    )
VAR previousLocation =
    CALCULATE (
        VALUES ( Table1[Location] ),
        ALLSELECTED ( Table1 ),
        Table1[ID] = currentID,
        Table1[Date] = previousDate
    )
RETURN
    IF (
        ISBLANK ( previousLocation ),
        BLANK (),
        IF (
            previousLocation <> currentLocation,
            1,
            BLANK ()
        )
    )

here

[#6]

This is absolutely awesome! Thank you so much for your work!
Instead of 1 in row 6 and 11 is it possible to move one prior to be in row 5 and row 10?