How to find the number of days between rows Saturday

[Joel_sony]

Hey guys! Can someone help me out how to create a new column which calculates the difference of days between each row similiar to the daydifference in this table?

[Anonymous · Accepted solution]

Hi @Joel_sony ,

You can refer to the following steps.
1. Go to power query editor and create new index column.

2. In power bi desktop, create a new calculated column and write the following expression.

DayDifference = 
    VAR CurrentResponseID = 'Table'[response_id]
    VAR CurrentCreatedAt = 'Table'[created_at]
    VAR PreviousCreatedAt = 
        CALCULATE(
            MAX('Table'[created_at]),
            FILTER(
                'Table',
                'Table'[response_id] = CurrentResponseID &&
                'Table'[id] < EARLIER('Table'[id])
            )
        )
    RETURN
        IF(
            ISBLANK(PreviousCreatedAt),
            0,
            ABS(DATEDIFF(PreviousCreatedAt, CurrentCreatedAt, DAY))
        )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Anonymous · Accepted solution]

Hi @Joel_sony ,

You can refer to the following steps.
1. Go to power query editor and create new index column.

2. In power bi desktop, create a new calculated column and write the following expression.

DayDifference = 
    VAR CurrentResponseID = 'Table'[response_id]
    VAR CurrentCreatedAt = 'Table'[created_at]
    VAR PreviousCreatedAt = 
        CALCULATE(
            MAX('Table'[created_at]),
            FILTER(
                'Table',
                'Table'[response_id] = CurrentResponseID &&
                'Table'[id] < EARLIER('Table'[id])
            )
        )
    RETURN
        IF(
            ISBLANK(PreviousCreatedAt),
            0,
            ABS(DATEDIFF(PreviousCreatedAt, CurrentCreatedAt, DAY))
        )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[divyed]

Hello @Joel_sony ,

Appreciate if you share logic behind the calculation. How are you finding daydifference ? 

Cheers

[Joel_sony]

This works based on this equation daydifference(created) = daydifference(created)-daydifference(started),
daydifference(started)=daydifference(started)-daydifference(submitted) and continue on until new response_id reaches, When new response_id reaches this process should restart. All values in positive only. 

[divyed · Accepted solution]

Hello @Joel_sony ,

Here is the dax for your requirement .

I have given 0 if next date in sequence is not available in case of change in response_id or end of data. Please modify as per need 

VAR CurrentDate = Tab_Response[Created_At]

VAR NextDate =

    CALCULATE(

        MIN(Tab_Response[Created_At]),

        FILTER(

            Tab_Response,

            Tab_Response[Response_id] = EARLIER(Tab_Response[Response_id]) &&

            (Tab_Response[Created_At] > CurrentDate ||

            (Tab_Response[Created_At] = CurrentDate && Tab_Response[ID] > EARLIER(Tab_Response[ID])))

        )

    )

RETURN

    IF(

        ISBLANK(NextDate),

        0,

        IF(

            DATEDIFF(CurrentDate, NextDate, DAY) = 0,

            0,

            DATEDIFF(CurrentDate, NextDate, DAY)

        )

    )

Did I answer your query ? Mark this as solution if this helps . Appreciate your Kudos.

Cheers.

Neeraj Kumar

[Angith_Nair]

Hi @Joel_sony 

Please elaborate and add what the new column should look like.

[Joel_sony]

This works based on this equation daydifference(created) = daydifference(created)-daydifference(started)
daydifference(started)=daydifference(started)-daydifference(submitted) and continue on until new response_id reaches, When new response_id reaches this process should restart. All values in positive only.  The same column(daydifference) as in the image