datediff between two separate columns and two diffrent record

[sapirmarko]

Hi everyone!

I have this table:

I want to create a measure that for each whs_id && outlet_id calculates the day difference between the current order to the previous order

for example:

whs_id = 59706 

outlet_id = 60809

the day difference between order 1453423 paid_date - 2/28/2023

to order 1456751 order_created_date - 1/30/2023 is -29 days, so the measure should give me "-29"

[v-rzhou-msft · Accepted solution]

Hi @sapirmarko ,

I suggest you to create a measure by below code.

Diff =
VAR _ADDRANK =
    ADDCOLUMNS (
        'Table',
        "Rank",
            RANKX (
                FILTER (
                    'Table',
                    'Table'[whs_id] = EARLIER ( [whs_id] )
                        && 'Table'[outlet_id] = EARLIER ( [outlet_id] )
                ),
                'Table'[order_ceated_date],
                ,
                DESC,
                DENSE
            )
    )
VAR _ADDDIFF =
    SUMMARIZE (
        _ADDRANK,
        [whs_id],
        [outlet_id],
        "Diff",
            VAR _CURRENT =
                MAXX (
                    FILTER (
                        _ADDRANK,
                        [whs_id] = EARLIER ( [whs_id] )
                            && [outlet_id] = EARLIER ( [outlet_id] )
                            && [Rank] = 1
                    ),
                    [order_ceated_date]
                )
            VAR _PREVIOUS =
                MAXX (
                    FILTER (
                        _ADDRANK,
                        [whs_id] = EARLIER ( [whs_id] )
                            && [outlet_id] = EARLIER ( [outlet_id] )
                            && [Rank] = 2
                    ),
                    [paid_date]
                )
            RETURN
                DATEDIFF ( _PREVIOUS, _CURRENT, DAY )
    )
RETURN
    SUMX ( _ADDDIFF, [Diff] )

My Sample:

Result is as below.

Best Regards,
Rico Zhou

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

[sapirmarko]

thanks!!