Count if consecutive values DAX

[Noredlac86]

Hi Team.

I was wondering if you can help me with this question

I have two 3 columns, i need to count how many time show "Yes" in each ID in the consecutive order months:

MonthNo    ID   Value
12                001   Yes
11                001   Yes
10                001   Yes
9                  001   Yes
12                002   Yes
10                002   Yes
12                003   Yes
11                003  No

Results:
ID 001= 4 
ID 002= 2
ID 003 =0

The column Month wont have all months,  for example the ID002, only have month 12 and 10, thise month are consecutive cause mont 11 does not show, does mean that if we have 1,3,5,10. This is also consecutive

What formula can i use?

[v-yanjiang-msft]

Hi @Noredlac86 ,

According to your description, here's my solution.

1.Add an index column in Power Query.

2.Create a calculated column.

Column =
VAR _Pre =
    MAXX (
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) - 1
        ),
        'Table'[Value]
    )
VAR _Next =
    MAXX (
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        ),
        'Table'[Value]
    )
RETURN
    IF ( [Value] = "Yes" && OR ( _Pre = "Yes", _Next = "Yes" ), 1 )

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Noredlac86]

Hi @v-yanjiang-msft 

Thanks for your time and reply. I was checking the formula and i think that "Value" column, is the column with the data "Yes" or "No"
I run the formula and i received:
"DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values."

[v-yanjiang-msft · Accepted solution]

Hi @Noredlac86 ,

The Value column is the column with the data "Yes" or "No". 

From your error message, I guess you use it as a measure. You can download my sample below to see the details.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hnguy71]

@Noredlac86 here's my attempt at it. Not sure if this is what you're going for but let's give it a try:

Flag Consecutive = 

VAR _ID = [ID]
VAR _Mval = [MonthNo]

VAR _NextMonth = CALCULATE( MIN(YOUR_TABLE[MonthNo]), FILTER(YOUR_TABLE, YOUR_TABLE[ID] = EARLIER(YOUR_TABLE[ID]) && YOUR_TABLE[MonthNo] > _Mval ))         // Find next month
VAR _NextValue = LOOKUPVALUE(YOUR_TABLE[Value], [ID], _ID, [MonthNo], _NextMonth)                                                                           // Return next month value

VAR _PrevMonth = CALCULATE( MAX(YOUR_TABLE[MonthNo]), FILTER(YOUR_TABLE, YOUR_TABLE[ID] = EARLIER(YOUR_TABLE[ID]) && YOUR_TABLE[MonthNo] < _Mval ))         // Find previous month
VAR _PrevValue = LOOKUPVALUE(YOUR_TABLE[Value], [ID], _ID, [MonthNo], _PrevMonth)                                                                           // return previous month value

RETURN
 
// some crazy logic switch. looking UP and DOWN to check if there is a consecutive pair.
SWITCH( TRUE(),
    [Value] = "Yes" && ISBLANK(_NextValue) && _PrevValue = "Yes", 1,
    [Value] = "Yes" && ISBLANK(_PrevValue) && _NextValue = "Yes", 1,
    [Value] = "Yes" && _NextValue = "Yes" && _PrevValue = "No", 1,
    [Value] = "Yes" && _PrevValue = "Yes", 1
)

[Noredlac86]

Hi @hnguy71 
Thanks for your time to reply my message.

I was checking and for your example, i need that sumarize the consecutive.

For example ID 001 should show number= 4. Cause they have 4 consecutive values in Yes.

How can change your code to show this data?

[hnguy71]

Hi @Noredlac86 ,

You don't change the code but rather create a measure:

Total Flagged = SUM(YOUR_TABLE[Flag Consecutive])

It'll add up all the 1s and you'll get your total value from there.

[Noredlac86]

Hi @hnguy71 

I add the new column "Total flagged, but show a strange result:

i think that i need to sum using the filter ID, but not sure how can write the formula.

I really appreciate if you can help on this.

Thanks!!

[v-yanjiang-msft]

Hi @Noredlac86 ,

If you mean @hnguy71 's solution, I notice that he said it's a measure but not a calculated column.

Best Regards,
Community Support Team _ kalyj

[Noredlac86]

HI   @v-yanjiang-msft 

ups, sorry. Thanks!

Do you know if its possible to add as a column?

Regards

[v-yanjiang-msft]

Hi @Noredlac86 ,

Please see the attached sample.

Best Regards,
Community Support Team _ kalyj

[Noredlac86]

thank. @v-yanjiang-msft .

In fact i need a new column with this data, cause i have more formulas and this formulas use that new column.

Do you know if there is a way to includ a new column with the formula?

REgards