HELP!! Calculate consecutive occurrences by group, reset with criteria

[jts_]

Hi, 

I've been struggling with the following calculation and am hoping can find possible solutions!

As the sample data show, I already have the data of customers' visit date, customer id, and the visit sequence of that customer. 

The last column, Consecutive_cals is my desired output

Visit Date Time	Cus_id	appt_status	Sequence	Completed_Appt_Sequence	Consecutive_calcs
5/25/2022	6448	Completed	1	1	0
6/1/2022	6448	No Show/ Cancel	2	1	1
6/2/2022	6448	No Show/ Cancel	3	1	2
6/3/2022	6448	Completed	4	2	0
6/8/2022	6448	No Show/ Cancel	5	2	1
6/21/2022	6448	No Show/ Cancel	6	2	2
6/24/2022	6448	No Show/ Cancel	7	2	3
6/29/2022	6448	No Show/ Cancel	8	2	4
6/30/2022	6448	No Show/ Cancel	9	2	5
6/5/2022	3221	No Show/ Cancel	1	0	1
6/7/2022	3221	Completed	2	1	0
6/9/2022	3221	Completed	3	1	0
7/3/2022	3221	No Show/ Cancel	4	1	1
7/11/2022	3221	No Show/ Cancel	5	1	2

I am looking for a solution that could calculate the consecutive occurrences of a customer for no-show appointments. Moreover, once there is a "completed" appointment occurred, the calculation will reset. Thank you in advance!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below measure and the attached pbix file.

It is for creating a measure.

Consecutive calcs measure: =
VAR _newtable =
    ADDCOLUMNS (
        ALL ( Data ),
        "@condition", IF ( CALCULATE ( MAX ( Data[appt_status] ) ) = "Completed", 0, 1 )
    )
VAR _result =
    SUMX (
        FILTER (
            _newtable,
            Data[Cus_id] = MAX ( Data[Cus_id] )
                && Data[Completed_Appt_Sequence] = MAX ( Data[Completed_Appt_Sequence] )
                && [@condition] <> 0
                && Data[Visit Date Time] <= MAX ( Data[Visit Date Time] )
        ),
        [@condition]
    )
RETURN
    IF (
        HASONEVALUE ( Data[Visit Date Time] ),
        IF ( MAX ( Data[appt_status] ) = "Completed", 0, _result )
    )

[jts_]

Hi @Jihwan_Kim ,

Yes! Your approach is exactly the solution I am looking for:)
I appreciate your help! Thanks a lot!!