Count consecutive days between months

[xrieraca]

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 month of the latest day.

I have this table:

Person	day	Assist
A	28/01/22	1
A	29/01/22	1
A	30/01/22	1
A	31/01/22	1
A	01/02/22	1
B	02/02/22	1
B	03/02/22	0
B	04/02/22	1
C	05/02/22	1
C	06/02/22	1
C	07/02/22	1

I want a result table like this:

Person	Month	Assistance
A	February	1
C	February	1

Any help would be very appreciate.

Many thanks.

[Anonymous · Accepted solution]

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.

[xrieraca]

It worked!

Many thanks,

[Jos_Woolley]

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

[Greg_Deckler]

@xrieraca You could use a repeating counter like Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211