cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oolamide85 Frequent Visitor
Frequent Visitor

Consecutive Night

I have this data set ine excel and I have these forumulas to check for duplicated as well check anyone whos worked consecutive Night and I need help with a dax expression for the excel formulas below.

Duplicate Check : = COUNTIFS(A:A,A2,B:B,B2)

Consecutive Nights : =IF(A2<>A1,1,IF(AND(C2=C1,B2-B1=1),E1+1,1))

 

I have also attached my sample daata

 

NameDateWeekNumDuplicate CheckConsecutive Nights
Ab Ale01/09/20183511
Ab Ale05/09/20183611
Ab Ale08/09/20183611
Ab Ale15/09/20183711
Ab Ale29/09/20183911
Ab Ale09/10/20184111
Ab Ale10/10/20184112
Ab Ale20/10/20184211
Aha Mor01/09/20183511
Aha Mor02/09/20183512


Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Consecutive Night

Hi @oolamide85,

 

Please check the steps as below.

 

1.Insert an index column in Power query.

 

2. Create a calculated column.

 

Column = 1

3. Create the measures as below.

 

Duplicate Check = 
VAR prename =
    CALCULATE (
        MAX ( Table1[Name] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
VAR predate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
RETURN
    IF (
        AND ( MAX ( Table1[Name] ) = prename, MAX ( Table1[Date] ) = predate ),
        0,
        1
    )
Consecutive Nights = 
VAR prename =
    CALCULATE (
        MAX ( Table1[Name] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
VAR predate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
VAR preweek =
    CALCULATE (
        MAX ( Table1[WeekNum] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
RETURN
    IF (
        AND (
            MAX ( Table1[WeekNum] ) = preweek,
            DATEDIFF ( predate, MAX ( Table1[Date] ), DAY ) = 1
        ),
        CALCULATE (
            SUM ( Table1[Column] ),
            FILTER (
                ALLEXCEPT ( Table1, Table1[Name] ),
                MAX ( Table1[Name] ) = prename
                    && Table1[WeekNum] = preweek
                    && DATEDIFF ( predate, MAX ( Table1[Date] ), DAY ) = 1
                    && Table1[Index] <= MAX ( Table1[Index] )
            )
        ),
        1
    )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Consecutive Night

Hi @oolamide85,

 

Please check the steps as below.

 

1.Insert an index column in Power query.

 

2. Create a calculated column.

 

Column = 1

3. Create the measures as below.

 

Duplicate Check = 
VAR prename =
    CALCULATE (
        MAX ( Table1[Name] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
VAR predate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
RETURN
    IF (
        AND ( MAX ( Table1[Name] ) = prename, MAX ( Table1[Date] ) = predate ),
        0,
        1
    )
Consecutive Nights = 
VAR prename =
    CALCULATE (
        MAX ( Table1[Name] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
VAR predate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
VAR preweek =
    CALCULATE (
        MAX ( Table1[WeekNum] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = MAX ( Table1[Index] ) - 1 )
    )
RETURN
    IF (
        AND (
            MAX ( Table1[WeekNum] ) = preweek,
            DATEDIFF ( predate, MAX ( Table1[Date] ), DAY ) = 1
        ),
        CALCULATE (
            SUM ( Table1[Column] ),
            FILTER (
                ALLEXCEPT ( Table1, Table1[Name] ),
                MAX ( Table1[Name] ) = prename
                    && Table1[WeekNum] = preweek
                    && DATEDIFF ( predate, MAX ( Table1[Date] ), DAY ) = 1
                    && Table1[Index] <= MAX ( Table1[Index] )
            )
        ),
        1
    )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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