How can I count consecutive month with dax

[newyearrrr]

I created a DAX measure:

VAR _TBL1 =

    ADDCOLUMNS (

        FILTER (

            SUMMARIZE (

                ALLSELECTED ( FACT_OT ),

                'FACT_OT'[EMP_CODE],

                'FACT_OT'[PERIOD],

                "_Check over", [CHECK_OT_OVER]

            ),

            [_Check over] <> BLANK ()

        ),

        "_month", MONTH ( FACT_OT[PERIOD] ),

        "_currentMonth", MONTH(MAX(FACT_OT[PERIOD]))

    )

RETURN FILTER(_TBL1 , OR(FACT_OT[EMP_CODE] = "04583",FACT_OT[EMP_CODE] = "01329") )

and then I get the results as shown in the table below.

but I want to count consecutive value by FACT_OT[PERIOD] , If it is a consecutive month, it will be counted.

Result : 

EMP_CODE: 01329  --->  Consecutive = 2

EMP_CODE: 04583  --->  Consecutive = 6

[Anonymous · Accepted solution]

Hi @newyearrrr 

First you need to sort the period column:

MonthRank = 
RANKX(
    FILTER(
        FACT_OT,
        FACT_OT[EMP_CODE] = EARLIER(FACT_OT[EMP_CODE])
    ),
    FACT_OT[PERIOD],
    ,
    ASC,
    Dense
)

Then you need to combined and sort the EMP_CODE and MonthRank column:

Combined = [EMP_CODE]&"-"&[MonthRank] 

Then you can use the following Measure to get the result you want:

Measure = 
VAR current_emp = SELECTEDVALUE(FACT_OT[EMP_CODE])
VAR current_month = CALCULATE(SELECTEDVALUE(FACT_OT[month]),FILTER('FACT_OT','FACT_OT'[EMP_CODE] = current_emp))
VAR current_rank = SELECTEDVALUE(FACT_OT[MonthRank])
VAR previous_month = CALCULATE(SELECTEDVALUE(FACT_OT[month]),FILTER(ALL('FACT_OT'),'FACT_OT'[MonthRank]=current_rank-1&&'FACT_OT'[EMP_CODE] = current_emp))
VAR next_month = CALCULATE(SELECTEDVALUE(FACT_OT[month]),FILTER(ALL('FACT_OT'),'FACT_OT'[MonthRank]=current_rank+1&&'FACT_OT'[EMP_CODE] = current_emp))
RETURN
IF(current_month - previous_month = 1||next_month-current_month=1,1,BLANK())

Result:

Best Regards,

Jayleny

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