cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors