Count consecutive values across columns where a value > x

[vanrensburga]

Good day

I am relatively new to PowerBI, and I have the following table with #High % (>=65%) calculated correctly.  I want to count the consecutive years across the columns where the % is >= 65%, and the output should be as per below (Column Name # Consecutive years%.  However, the output I get is shown in bold, which is incorrect.  For the # consecutive, I created a Consecutive Years Temp with the following dax (see below the table) and then another measure for #consecutive years%.  What am I doing wrong?

Yr 1	Yr 2	Yr 3	Yr 4	Yr 5	# High % (5Yrs)	# Consecutive years %
13436%	40%	721%	4%	17%	2	1 2
2406%	64%	99%	5%	376%	3	1 4
166%	64%	74%	17%	349%	3	1 2
135%	151%	6%	45%	13%	2	2 2
71%	112%	27%	113%	70%	4	2 2

--1st Measure

ConsecutiveYearsTemp =

IF (

    'CPR'[YR1%] > 0.65

        || 'CPR'[YR2%] > 0.65

        || 'CPR'[YR3%] > 0.65

        || 'CPR'YR4%] > 0.65

        || 'CPR'[YR5%] > 0.65,

    1,

    0

)

--2nd Measure

ConsecutiveYears =

CALCULATE (

    MAXX (

        FILTER (

            ALL ( 'CPR' ),

            'CPR'[CurrentPeriodExpiryDate] <= 'CPR'[CurrentPeriodExpiryDate]

                && 'CPR'[YR1%] > 0.65

        ),

        'CPR'[ConsecutiveYearsTemp] + 1

    ),

    'CPR'[ConsecutiveYearsTemp] + 1

)

Cheers

[amitchandak · Accepted solution]

@vanrensburga , This should be end

IF (
'CPR'[YR1%] > 0.65
&& 'CPR'[YR2%] > 0.65
&& 'CPR'[YR3%] > 0.65,
1,
0
)

Also, you can try measures like
if( CALCULATE(Countx(filter(Values('Date'[Year]), [%] >.65), [Year]) , WINDOW(-2,REL, 0, REL, ALLSELECTED('Date'[Year]),ORDERBY([Year],Desc))) >=3,1,0)

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[vanrensburga]

Thank you very much.