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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.