cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

Go to My LinkedIn Page

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.

Go to My LinkedIn Page

Helper I

Hi @Jihwan_Kim ,

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors