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

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