Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
newyearrrr
Frequent Visitor

how to calculate continuous month from parameter value

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

Hi All,
I have table data like this :

 

newyearrrr_0-1718869519573.png

 

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.

newyearrrr_1-1718869764486.png

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 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
        )
    )

vkaiyuemsft_0-1718942902171.png

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
        )
    )

vkaiyuemsft_0-1718942902171.png

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.

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.
 
newyearrrr_0-1719228197195.png

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_1-1719228886065.png

 

 

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
 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.