cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## 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.

Helper I

Hi @Jihwan_Kim ,

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors