cancel
Showing results for
Did you mean:
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
Super User

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 =
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 )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Super User

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 =
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 )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Hi @Jihwan_Kim ,

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors