cancel
Showing results for 
Search instead for 
Did you mean: 

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

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.


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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

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