cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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:

image.png

   And Rules are(as per table data):

        1. Need to calculate time when transaction will be with department Dpt001.

        2. Time should be calculated within office hours only from 9 Am to 6 PM.

        3. Exclude any weekends or public holiday (holiday input will be separate list of holidays (a column in other table) in data model).

        4. E.g.-for transaction T01,It will start calculating time from row 1- stop at row 2. start at row 3 -stop at row 5, start at row 7 - calculate till current time. For Tx T002, it will start at row 9- stop at row 10, start at 12-stop at 14

 

Assuming 9th Oct 2020 is public holiday and currnet date-time is 20 Oct 2020 12:00 PM, I have manually calculated as below (with comments) to make rules more clearer in 4th column 'TimeWithDpt001' for 2 transaction numbers. Also put comments to understnad the calculation:

Row NoTransactionNoTransactionSentTimeTransactionSentToTimeWithDpt001       Comments for calculations
1T0129/9/2020 11:15Dpt001 Calculation will start from 29 Sep 2020 11:15 AM, as it went to Dpt001
2T012/10/2020 9:30Dpt000225:15before sent to Dpt0002, Time with Dpt001 is 25 hours 30 minutes (only time between 9 to 6 calculation)
3T017/10/2020 15:45Dpt001 Time start for Dpt001.
4T0110/10/2020 10:05Dpt001 Assuming 9 Oct is public holiday and 10, 11 oct is weeked, these 3 days will not be considered for calculation TimeWith Dpt001
5T0113/10/2020 12:15Dpt000223:30once it sent to Dpt0002,calculation stops. before sent to Dpt0002, Time with Dpt001 is 23 hours 30 minutes
6T0114/10/2020 13:25Dpt0002 ignore calculation as it is stillwith Dpt0002
7T0115/10/2020 8:45Dpt00130:00Calculation start from 15Oct 9AMnot from 8:45AM due to work hours) and will continue till current date-time(20 Oct 2020 12:00 PM). skip weekend(17, 18 Oct)
8T00215/9/2020 7:55Dpt0002 no calculation as transaction starting with Dpt0002
9T00222/9/2020 18:13Dpt001 Time calculation will NOT starts on 22 Sep 2020 at 6:13 PM as work hours end at 6 PM. It starts on 23 Sep 2020 9AM.
10T00210/10/2020 0:45Dpt0002108:00It will calculate till 8 Oct 2020 6PM as 9 Oct is public holiday and 10 Oct is weekend.
11T00210/10/2020 18:15Dpt0002 skip
12T00211/10/2020 13:10Dpt001 Start time calculation from 12 OCT 2020 9 AM as 11 Oct is Sunday
13T00215/10/2020 11:25Dpt001 continue calculation
14T00216/10/2020 12:50Dpt000239:50Calculation ends once transaction sent to Dpt0002

 

 

Anyone can please help me to suggest DAX/calculated column /power query to achieve the same? I tried out multiple DAX functions but nothing giving desired outcome. any hints /tips are welcome.

 

Thanks in Advance

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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
    )

001.JPG

 

 

Best regards

Icey

 

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

View solution in original post

6 REPLIES 6
Rajaganapathy
Frequent Visitor

@Icey  - The logics looks great. I have a similar use case, where I need to calculate only the days difference instead hours calculation.

I have been reading your code for the past three to four days. As a beginner to PowerBi, I couldn't decode and understand all of your DAX measures, some codes yes.

Can you please help me understand your codes - By adding comments to each line of codes? I will be thankful to you lot.

Raja from India

Icey
Community Support
Community Support

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
    )

001.JPG

 

 

Best regards

Icey

 

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

Anonymous
Not applicable

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

Hi ALL, @Icey 

by making both columns data type strictly 'Date' type and format 'd/m/yyyy', it got resolved

Thanks

BlueTeam1
Frequent Visitor

Hello @Anonymous

What would be the final format? Would this be in Excel or Power BI?
Also, can you edit the data beforehand? Add columns or reorganize it before importing it into Power Query/DAX etc.?

Anonymous
Not applicable

Hi @BlueTeam1 , Thanks for your comment.

And sorry I missed some information.

 

This data is getting imported from excel into Power BI. Yes, we can edit data, add /reorganize columns in excel and power BI both but final column (4th column ) should come in PowerBI Query/Desktop.

Format of the 4th Column should be as 'H:MM' format i.e '3:30' will represent 3 hours 30 minutes OR '23:12' will represent 23hours 12minutes.

But even we can calculate it only in 'minutes' (as integer) such as 3:30 can be calculated as 210 (in minutes). that also should be fine as later I can convert to required format.

 

Thanks in Advance,

Komal

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors