Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
Hi @Jihwan_Kim ,
Yes! Your approach is exactly the solution I am looking for:)
I appreciate your help! Thanks a lot!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |