Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
jts_
Helper I
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 TimeCus_idappt_statusSequenceCompleted_Appt_SequenceConsecutive_calcs
5/25/20226448Completed110
6/1/20226448No Show/ Cancel211
6/2/20226448No Show/ Cancel312
6/3/20226448Completed420
6/8/20226448No Show/ Cancel521
6/21/20226448No Show/ Cancel622
6/24/20226448No Show/ Cancel723
6/29/20226448No Show/ Cancel824
6/30/20226448No Show/ Cancel925
      
6/5/20223221No Show/ Cancel101
6/7/20223221Completed210
6/9/20223221Completed310
7/3/20223221No Show/ Cancel411
7/11/20223221No Show/ Cancel512

 

 

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
Jihwan_Kim
Super User
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 =
    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 )
    )

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
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 =
    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 )
    )

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hi @Jihwan_Kim ,

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

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.