how to calculate continuous month from parameter value

[newyearrrr]

Example Power BI File: Dropbox - POWER BI - Simplify your life 

Hi All,
I have table data like this :

and I create a parameter value to be used to decide values ​​from the column "Total" in Table Data 
as shown and explain in the below.

For explain : 

1. if " TOTAL " >= " parameter value "  is OVER  ( CHECK_OVER = 1 )
2. The current month is "Jun2024"  ( FLAG_CURRENT = 1 )
3. AA : in current month is OVER
   BB : in current month is NOT OVER
4. Therefore, in the current month there is 1 person over.

Need Help : I would like to know how many continuous months OVER has been running by EMP_NAME? 

From the data example is
       AA  : Continueus 2 months is -> May2024 and Jun2024
       BB  :  Discontinuous

Example Power BI File: Dropbox - POWER BI - Simplify your life 

[Anonymous · Accepted solution]

Hi @newyearrrr ,

You can create a measure.

Measure2 = 
VAR _currentmonth =
    MONTH ( MAX ( [PERIOD] ) )
VAR _currentName =
    SELECTEDVALUE ( Data[EMP_NAME] )
VAR _vtable =
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( Data ),
                'Data'[EMP_NAME],
                'Data'[PERIOD],
                "_Check over", 'Data'[CHECK_OVER]
            ),
            [_Check over] <> BLANK ()
        ),
        "_month", MONTH ( [PERIOD] )
    )
VAR _vtable2 =
    FILTER (
        ADDCOLUMNS (
            _vtable,
            "_diff",
                MAXX ( FILTER ( _vtable, [EMP_NAME] = EARLIER ( Data[EMP_NAME] ) ), [_month] ) - [_month] + 1,
            "_MoreRow",
                COUNTROWS (
                    FILTER (
                        _vtable,
                        [EMP_NAME] = EARLIER ( [EMP_NAME] )
                            && [_month] >= EARLIER ( [_month] )
                    )
                )
        ),
        [_MoreRow] = [_diff]
    )
RETURN
    IF (
        _currentmonth
            = CALCULATE (
                MONTH ( MAX ( 'Data'[PERIOD] ) ),
                FILTER ( ALLSELECTED ( Data ), 'Data'[EMP_NAME] = _currentName )
            ),
        IF (
            [CHECK_OVER] = 1,
            COUNTROWS ( FILTER ( _vtable2, [EMP_NAME] = SELECTEDVALUE ( Data[EMP_NAME] ) ) ),
            0
        )
    )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[newyearrrr]

Dear Clara Gong,

How can I count consecutive month. 😥

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

[newyearrrr]

Hi Clara Gong,

Superb!

Thank you very much for your help. I think that's work to do.

---

Current Period is latest month in data table.

If it's next month ,

then the Current Period will be Jul 2024 and
FLAG_CURRENT  = 1 in Jul 2024  

FLAG_CURRENT  = 0 in Jun 2024