HELP, Dax formula for Current Streak

[Anonymous]

I am workign through a formula that is intended to show the current streak for a given hospital that is below 75% in theor satisfaction scores.  I have an accurate formula for the LONGEST streak but cannot figure out how to make ti the current streak??

Dat a is 1 row per hospital and fiscal period in columns oldest to newest.

Consecutive Period Under 75% =

SWITCH (

TRUE (),

'OSAT Streak (2)'[Under 75%] = 1, 0,

CALCULATE (

COUNTROWS ( 'OSAT Streak (2)' ),

FILTER (

ALL ( 'OSAT Streak (2)' ),

'OSAT Streak (2)'[Under 75%] = 1

&& 'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )

&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )

)

)

= 0, CALCULATE (

SUM ( 'OSAT Streak (2)'[Under 75%] ),

FILTER (

ALL ( 'OSAT Streak (2)' ),

'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )

&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )

)

),

CALCULATE (

SUM ( 'OSAT Streak (2)'[Under 75%] ),

FILTER (

ALL ( 'OSAT Streak (2)' ),

'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )

&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]

> CALCULATE (

MAX ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] ),

FILTER (

ALL ( 'OSAT Streak (2)' ),

'OSAT Streak (2)'[Under 75%] = 0

&& 'OSAT Streak (2)'[Hosp Num] = EARLIEST ( 'OSAT Streak (2)'[Hosp Num] )

&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] < EARLIEST ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )

)

)

&& 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] <= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )

)

)

)

[andre]

here is a video on how to create a measure to do Streak analysis without doing anything with calculated columns:

[Anonymous]

Sample Data set below, using these two examples I would want to see a 0 for #47 and a 3 for #59

Mars Fiscal Yr and Pd	Hosp Num	Overall Satisfaction - All Clients	Mars Fiscal Mth End Dt
201706	47	78.95%	6/17/2017
201707	47	80.85%	7/15/2017
201708	47	76.47%	8/12/2017
201709	47	80.36%	9/9/2017
201710	47	75.00%	10/7/2017
201711	47	74.63%	11/4/2017
201712	47	69.33%	12/2/2017
201713	47	66.67%	12/30/2017
201801	47	70.67%	1/27/2018
201802	47	68.75%	2/24/2018
201803	47	77.33%	3/24/2018
201804	47	75.71%	4/21/2018
201805	47	91.04%	5/19/2018
201806	47	88.89%	6/16/2018
201807	47	92.31%	7/14/2018
201808	47	86.90%	8/11/2018
201809	47	86.42%	9/8/2018
201810	47	80.72%	10/6/2018
201811	47	77.01%	11/3/2018
201812	47	76.25%	12/1/2018
201813	47	77.33%	12/29/2018
201901	47	80.00%	1/26/2019
201902	47	75.93%	2/23/2019
201903	47	76.52%	3/23/2019
201904	47	73.88%	4/20/2019
201905	47	76.72%	5/18/2019
201906	47	82.05%	6/15/2019
201907	47	83.81%	7/13/2019
201908	47	81.48%	8/10/2019
201909	47	81.65%	9/7/2019
201910	47	89.19%	10/5/2019
201911	47	89.19%	11/2/2019
201912	47	91.26%	11/30/2019
201913	47	86.32%	12/28/2019
202001	47	87.23%	1/25/2020
202002	47	81.61%	2/22/2020
201706	59	68.75%	6/17/2017
201707	59	70.59%	7/15/2017
201708	59	69.05%	8/12/2017
201709	59	75.00%	9/9/2017
201710	59	68.60%	10/7/2017
201711	59	67.68%	11/4/2017
201712	59	61.80%	12/2/2017
201713	59	72.53%	12/30/2017
201801	59	79.07%	1/27/2018
201802	59	81.55%	2/24/2018
201803	59	78.35%	3/24/2018
201804	59	72.22%	4/21/2018
201805	59	66.67%	5/19/2018
201806	59	56.79%	6/16/2018
201807	59	68.54%	7/14/2018
201808	59	67.59%	8/11/2018
201809	59	71.68%	9/8/2018
201810	59	67.86%	10/6/2018
201811	59	66.67%	11/3/2018
201812	59	70.53%	12/1/2018
201813	59	68.60%	12/29/2018
201901	59	69.09%	1/26/2019
201902	59	68.38%	2/23/2019
201903	59	71.69%	3/23/2019
201904	59	76.32%	4/20/2019
201905	59	81.05%	5/18/2019
201906	59	83.33%	6/15/2019
201907	59	78.63%	7/13/2019
201908	59	72.58%	8/10/2019
201909	59	69.13%	9/7/2019
201910	59	74.03%	10/5/2019
201911	59	76.76%	11/2/2019
201912	59	78.70%	11/30/2019
201913	59	70.65%	12/28/2019
202001	59	68.13%	1/25/2020
202002	59	70.09%	2/22/2020

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Thank you for your additional information, we can try to create a measure to meet your requirement:

Consecutive Measure = 
VAR LastUnderDate =
    CALCULATE (
        MAX ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 0
                && 'OSAT Streak (2)'[Hosp Num] in DISTINCT('OSAT Streak (2)'[Hosp Num])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= MAX('OSAT Streak (2)'[Mars Fiscal Mth End Dt])
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'OSAT Streak (2)' ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 1
                && 'OSAT Streak (2)'[Hosp Num]  in DISTINCT('OSAT Streak (2)'[Hosp Num])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= MAX('OSAT Streak (2)'[Mars Fiscal Mth End Dt])
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >= LastUnderDate
        )
    )+0

Best regards,

[Anonymous]

This worked like a charm!  Thamnk you so much!

[v-lid-msft]

Hi @Anonymous ,

We can try to create a calculated column to get the consecutive days:

Consecutive Measure =
VAR LastUnderDate =
    CALCULATE (
        MAX ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 1
                && 'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'OSAT Streak (2)' ),
        FILTER (
            'OSAT Streak (2)',
            'OSAT Streak (2)'[Under 75%] = 0
                && 'OSAT Streak (2)'[Hosp Num] = EARLIER ( 'OSAT Streak (2)'[Hosp Num] )
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt]
                    <= EARLIER ( 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] )
                && 'OSAT Streak (2)'[Mars Fiscal Mth End Dt] >= LastUnderDate
        )
    )

If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive For Business and share the link here.

Best regards,

[amitchandak]

@Anonymous 

Can you share sample data and sample output.