cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jts_
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 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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


Hi @Jihwan_Kim ,

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.