Calculate time difference of 2 dates in conditional scenario

Hi Anonymous ,

I made it !😁 Please check and any other methods please advise.

1. Create a date column and then create relationship between your TransactionLog table and your dates table based on the date column.

2. Modified TransactionSentTime.

ModifiedSentTime = 
SWITCH (
    [TransactionSentTo],
    "Dpt001",
        SWITCH (
            TRUE (),
            RELATED ( 'Calendar'[WeekDay] ) = 7,
                RELATED ( 'Calendar'[Date] ) + 1
                    + TIME ( 9, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) = 6,
                RELATED ( 'Calendar'[Date] ) + 2
                    + TIME ( 9, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) = 5
                && [TransactionSentTime]
                    >= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
                RELATED ( 'Calendar'[Date] ) + 3
                    + TIME ( 9, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4, 5 }
                && [TransactionSentTime]
                    <= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ), RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4 }
                && [TransactionSentTime]
                    >= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
                RELATED ( 'Calendar'[Date] ) + 1
                    + TIME ( 9, 0, 0 ),
            [TransactionSentTime]
        ),
    "Dpt0002",
        SWITCH (
            TRUE (),
            RELATED ( 'Calendar'[WeekDay] ) = 7,
                RELATED ( 'Calendar'[Date] ) - 2
                    + TIME ( 18, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) = 6,
                RELATED ( 'Calendar'[Date] ) - 1
                    + TIME ( 18, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) = 1
                && [TransactionSentTime]
                    <= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
                RELATED ( 'Calendar'[Date] ) - 3
                    + TIME ( 18, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) IN { 2, 3, 4, 5 }
                && [TransactionSentTime]
                    <= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
                RELATED ( 'Calendar'[Date] ) - 1
                    + TIME ( 18, 0, 0 ),
            RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4, 5 }
                && [TransactionSentTime]
                    >= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ), RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
            [TransactionSentTime]
        )
)

3. Create TimeWithDpt001 column.

TimeWithDpt001 column = 
VAR t =
    FILTER ( TransactionLog, [TransactionNo] = EARLIER ( [TransactionNo] ) )
VAR PreviousDpt0002No =
    MAXX (
        FILTER ( t, [Row No] < EARLIER ( [Row No] ) && [TransactionSentTo] = "Dpt0002" ),
        [Row No]
    )
VAR LastDpt0002No =
    MINX (
        FILTER (
            t,
            [Row No] <= EARLIER ( [Row No] )
                && [Row No] > PreviousDpt0002No
                && [TransactionSentTo] = "Dpt0002"
        ),
        [Row No]
    )
VAR FirstRowNo =
    CALCULATE (
        FIRSTNONBLANK ( TransactionLog[Row No], 1 ),
        FILTER (
            TransactionLog,
            TransactionLog[TransactionNo] = EARLIER ( TransactionLog[TransactionNo] )
        )
    )
VAR LastRowNo =
    CALCULATE (
        LASTNONBLANK ( TransactionLog[Row No], 1 ),
        FILTER (
            TransactionLog,
            TransactionLog[TransactionNo] = EARLIER ( TransactionLog[TransactionNo] )
        )
    )
VAR StartDateTime_ =
    IF (
        [Row No] = FirstRowNo
            && [TransactionSentTo] = "Dpt0002",
        BLANK (),
        MINX (
            FILTER ( t, [TransactionSentTo] = "Dpt001" && [Row No] > PreviousDpt0002No ),
            [ModifiedSentTime]
        )
    )
VAR ModifiedNow =
    SWITCH (
        TRUE (),
        WEEKDAY ( TODAY () ) = 7,
            TODAY () - 2
                + TIME ( 18, 0, 0 ),
        WEEKDAY ( TODAY () ) = 6,
            TODAY () - 1
                + TIME ( 18, 0, 0 ),
        WEEKDAY ( TODAY () ) = 1
            && NOW ()
                <= TODAY () + TIME ( 9, 0, 0 ),
            TODAY () - 3
                + TIME ( 18, 0, 0 ),
        WEEKDAY ( TODAY () ) IN { 2, 3, 4, 5 }
            && NOW ()
                <= TODAY () + TIME ( 9, 0, 0 ),
            TODAY () - 1
                + TIME ( 18, 0, 0 ),
        WEEKDAY ( TODAY () ) IN { 1, 2, 3, 4, 5 }
            && NOW ()
                >= TODAY () + TIME ( 18, 0, 0 ), TODAY () + TIME ( 18, 0, 0 ),
        NOW ()
    )
VAR EndDateTime_ =
    IF (
        [Row No] = FirstRowNo
            && [TransactionSentTo] = "Dpt0002",
        BLANK (),
        MINX (
            FILTER ( t, [TransactionSentTo] = "Dpt0002" && [Row No] > PreviousDpt0002No ),
            [ModifiedSentTime]
        )
    )
VAR EndDateTime_2 =
    IF (
        [Row No] = LastRowNo
            && [TransactionSentTo] = "Dpt001",
        ModifiedNow,
        IF (
            [Row No] = LastRowNo
                && [TransactionSentTo] = "Dpt0002",
            [ModifiedSentTime],
            EndDateTime_
        )
    )
VAR DateDiff_m =
    DATEDIFF ( StartDateTime_, EndDateTime_2, MINUTE )
VAR RestDayCount =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        FILTER (
            'Calendar',
            'Calendar'[Date] >= StartDateTime_
                && 'Calendar'[Date] <= EndDateTime_2
                && (
                    [WeekDay] IN { 6, 7 }
                        || [Holiday(just for test)] <> BLANK ()
                )
        )
    ) + 0
VAR DayDiff =
    DATEDIFF ( StartDateTime_, EndDateTime_2, DAY )
VAR ModifiedDiff = DateDiff_m - RestDayCount * 9 * 60 - DayDiff * 15 * 60
VAR HHMM =
    IF (
        ModifiedDiff = BLANK (),
        BLANK (),
        INT ( ModifiedDiff / 60 ) & ":"
            & FORMAT ( MOD ( ModifiedDiff, 60 ), "00" )
    )
RETURN
    IF (
        [Row No] = LastRowNo
            || ( NOT ( LastDpt0002No = PreviousDpt0002No + 1 )
            && [Row No] = LastDpt0002No ),
        HHMM
    )

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.