How can I count consecutive month with dax

newyearrrr — Mon, 24 Jun 2024 13:57:23 GMT

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

Re: How can I count consecutive month with dax

Anonymous — Tue, 25 Jun 2024 04:07:44 GMT

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.

topic Re: How can I count consecutive month with dax in DAX Commands and Tips

How can I count consecutive month with dax

Re: How can I count consecutive month with dax