Forum Discussion

sapirmarko's avatar
sapirmarko
Helper I
3 years ago
Solved

datediff between two separate columns and two diffrent record

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"

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.