Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

Calculate time difference of 2 dates in conditional scenario

Hi All, I got requirement to calculate time difference in "H:MM" format between two dates with specific rules in 4th column. Below is sample table format with 3 columns: And Rules are(as ...

Need Help

Tips and Tricks

Icey

5 years ago

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.

Calculate time difference of 2 dates in conditional scenario.pbix116 KB

Icey

Community Support

5 years ago

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.

Calculate time difference of 2 dates in conditional scenario.pbix116 KB

Anonymous
Not applicable
5 years ago
Hello Icey , Many thanks for reverting back with solution. this is great.
and my apologies for delay response on the same.
Logic looks promising. I was trying to implement the same and got wrong calculated time for only some of rows. When checked, I noticed ModifiedSentTime is not working in my case. column is NOT giving any error.
For SWITCH statement , it is always calculating 'False' for every condition and returning [TransactionSentTime] Always. Also it is actually not bringing values from Calendar table (either date or weekDay). I checked the relationship it is fine.
I also tried to convert SWITCH to IF statement but same issue while comparing.
Can you please guide what may be wrong here?
- Anonymous
  Not applicable
  5 years ago
  Hi ALL, Icey
  by making both columns data type strictly 'Date' type and format 'd/m/yyyy', it got resolved
  Thanks

Forum Discussion

Calculate time difference of 2 dates in conditional scenario

Related content

Dynamic Calculation Different Period

Different Calculations

Conditional formatting on calculated item in calculation group

Conditional formatting for difference between bars?

conditional Formatting in Calculation Item