Forum Discussion

oolamide85's avatar
oolamide85
Frequent Visitor
7 years ago
Solved

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.

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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
    )

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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
    )

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank