Forum Discussion

xrieraca's avatar
xrieraca
Frequent Visitor
4 years ago
Solved

Count consecutive days between months

Hi Im pretty new to Power BI and I can't think how to do a measure to calculate this.   I want to calculate if someone has assist 3 to 5 days in a row, an if its true, count 1 asisstance in the mon...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi xrieraca ,

     

    You can create two measures to get it.

    Assistance =
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Assist] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) )
    )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Assist] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) )
    )
RETURN
    IF ( _count = _sum, 1 )

    Month =
FORMAT (
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) ),
        [day]
    ),
    "mmmm"
)

    Note:set the Assistance measure show items when the value is 1.

     

     

     

    Best Regards,

    Stephen Tao

     

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

Jos_Woolley's avatar
Jos_Woolley
Solution Sage
4 years ago

Perhaps these two measures:

Assistance =
VAR MyTable =
    ADDCOLUMNS(
        ADDCOLUMNS(
            'Table',
            "Concat",
                CONCATENATEX(
                    FILTER(
                        'Table',
                        'Table'[Person] = EARLIER( 'Table'[Person] )
                            && 'Table'[day] <= EARLIER( 'Table'[day] )
                    ),
                    'Table'[Assist],
                    ,
                    'Table'[day], ASC
                )
        ),
        "Check",
            0
                + ( VALUE( RIGHT( [Concat], 3 ) ) = 111 )
    )
RETURN
    0
        + (
            SUMX( MyTable, 0 + ( [Check] > 0 ) ) > 0
        )

 

Month =
VAR MyTable =
    ADDCOLUMNS(
        ADDCOLUMNS(
            'Table',
            "Concat",
                CONCATENATEX(
                    FILTER(
                        'Table',
                        'Table'[Person] = EARLIER( 'Table'[Person] )
                            && 'Table'[day] <= EARLIER( 'Table'[day] )
                    ),
                    'Table'[Assist],
                    ,
                    'Table'[day], ASC
                )
        ),
        "Check",
            0
                + ( VALUE( RIGHT( [Concat], 3 ) ) = 111 )
    )
RETURN
    FORMAT( MAXX( MyTable, IF( [Check] > 0, 'Table'[day] ) ), "mmmm" )

which can then be placed into, for example, a simple Table visual alongside the Person field.

Regards