I need assistance writing a DAX function to calculate the difference in hours between two timestamps

[durgaraop21]

I need assistance writing a DAX function to calculate the difference in hours between two timestamps(Date, Lead Date) excluding weekends.

Please find below sample data.

 

Number		state		Date		Lead Date		Hours_Including weekends
RI9925		Open		19-03-2024 06:39		23-03-2024 12:39		102
RI9925		Awaiting Info		23-03-2024 12:39		24-03-2024 00:39		12
RI9925		Work in Progress		24-03-2024 00:39		14-05-2024 12:40		1236
RI9925		Pending		14-05-2024 12:40		17-06-2024 18:40		822
RI9925		Closed Complete		17-06-2024 18:40
RI7295		Open		14-03-2024 21:40		26-03-2024 03:39		270
RI7295		Awaiting Info		26-03-2024 03:39		04-06-2024 09:42		1686
RI7295		Closed Complete		04-06-2024 09:42

[v-xingshen-msft]

Hi All,
Firstly  Kaviraj11 and d_rohlfs thank you for yours solution!
And @durgaraop21 ,When you encounter a Calendar function that displays a blank value because your date Date has a blank value, we can add the following filter to screen out the blank value, which will ensure that the Calendar function is used correctly

VAR StartDate=CALCULATE(MAX('generated_data'[Date]),FILTER('generated_data','generated_data'[Lead Date]<>BLANK()))
VAR EndDate=CALCULATE(MAX('generated_data'[Lead Date]),FILTER('generated_data','generated_data'[Lead Date]<>BLANK()))

When your deadline is on a weekend, but the weekend date can't all be 24:00:00, so we can't simply calculate how many weekends there are and then multiply it by 24, which may cause errors, so in the following code I separated two cases, one when the deadline is on a weekend, and one when the deadline isn't on a weekend, which can be better and more detailed to divide the different cases.

VAR Endofweek=WEEKDAY(EndDate,2)
VAR AdjustedEndDate=
IF(
    Endofweek>5,
    DATE(YEAR(EndDate),MONTH(EndDate),DAY(EndDate))-DATE(YEAR((Endofweek-5)),MONTH((Endofweek-5)),DAY((Endofweek-5)))+TIME(23,0,0),
    EndDate
)//If the deadline falls on a weekend, go back to 23:00 on the last working day of the deadline
VAR WeekDays=
COUNTROWS(
    FILTER(
        ADDCOLUMNS(CALENDAR(StartDate,EndDate),"DayofWeek",WEEKDAY([Date],2)),
        [DayofWeek]>5&&Endofweek<=5)
)//Calculate the number of days in the weekend between the two dates if the closing date does not fall on a weekend
VAR WeekDays2=COUNTROWS(
    FILTER(
        ADDCOLUMNS(CALENDAR(StartDate,EndDate),"DayofWeek",WEEKDAY([Date],2)),
        [DayofWeek]>5&&AdjustedEndDate)
)//Calculate what happens if the deadline falls on a weekend, and then add a restriction to see how many weekends are in the dates

According to the second line of your example data, your start time is Saturday and the end time is Sunday, so I added a judgement here, if the start time is Saturday and the end date is on Sunday, we give 0, I don't know if this is helpful to you, if it is not, you can also delete this judgement condition, it has no effect on the other parts.

RETURN
IF(
    StartDate=AdjustedEndDate||StartDate>AdjustedEndDate,//Determine if it's on the same day, and also if both dates fall on a weekend.
    IF(
    WEEKDAY(StartDate,2)<=5,
    DATEDIFF(StartDate,EndDate,HOUR),
    0),
    IF(
        Endofweek<=5,//Determine if the end date is on a weekend
        DATEDIFF(StartDate,EndDate,HOUR)-WeekDays*24,//If it is determined that the end date does not fall on a weekend, we simply count the number of weekends between dates at this point.
        DATEDIFF(StartDate,AdjustedEndDate,HOUR)-WeekDays*24 //But if the end date is on a weekend, use AdjustedEndDate to get to the last working day of the end date and then subtract the intervening weekend time.
     )
)

Below is all the complete code, there are some complex I have added comments to help you understand, I hope to help you with your questions!

Measure = 
VAR StartDate=CALCULATE(MAX('generated_data'[Date]),FILTER('generated_data','generated_data'[Lead Date]<>BLANK()))
VAR EndDate=CALCULATE(MAX('generated_data'[Lead Date]),FILTER('generated_data','generated_data'[Lead Date]<>BLANK()))
VAR Endofweek=WEEKDAY(EndDate,2)
VAR AdjustedEndDate=
IF(
    Endofweek>5,
    DATE(YEAR(EndDate),MONTH(EndDate),DAY(EndDate))-DATE(YEAR((Endofweek-5)),MONTH((Endofweek-5)),DAY((Endofweek-5)))+TIME(23,0,0),
    EndDate
)//If the deadline falls on a weekend, go back to 23:00 on the last working day of the deadline
VAR WeekDays=
COUNTROWS(
    FILTER(
        ADDCOLUMNS(CALENDAR(StartDate,EndDate),"DayofWeek",WEEKDAY([Date],2)),
        [DayofWeek]>5&&Endofweek<=5)
)//Calculate the number of days in the weekend between the two dates if the closing date does not fall on a weekend
VAR WeekDays2=COUNTROWS(
    FILTER(
        ADDCOLUMNS(CALENDAR(StartDate,EndDate),"DayofWeek",WEEKDAY([Date],2)),
        [DayofWeek]>5&&AdjustedEndDate)
)//Calculate what happens if the deadline falls on a weekend, and then add a restriction to see how many weekends are in the dates

RETURN
IF(
    StartDate=AdjustedEndDate||StartDate>AdjustedEndDate,//Determine if it's on the same day, and also if both dates fall on a weekend.
    IF(
    WEEKDAY(StartDate,2)<=5,
    DATEDIFF(StartDate,EndDate,HOUR),
    0),
    IF(
        Endofweek<=5,//Determine if the end date is on a weekend
        DATEDIFF(StartDate,EndDate,HOUR)-WeekDays*24,//If it is determined that the end date does not fall on a weekend, we simply count the number of weekends between dates at this point.
        DATEDIFF(StartDate,AdjustedEndDate,HOUR)-WeekDays*24 //But if the end date is on a weekend, use AdjustedEndDate to get to the last working day of the end date and then subtract the intervening weekend time.
     )
)

If you still have questions I will upload my pbix file and maybe it will help you with your doubts, I would be extremely proud if I could help you out!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Kaviraj11 · Accepted solution]

Please try this out

DifferenceInHours =
VAR StartDate = 'Table'[Date]
VAR EndDate = 'Table'[Lead Date]
VAR TotalHours =
IF(
ISBLANK(StartDate) || ISBLANK(EndDate),
BLANK(),
DATEDIFF(StartDate, EndDate, HOUR)
)
VAR TotalDays =
IF(
ISBLANK(StartDate) || ISBLANK(EndDate),
BLANK(),
DATEDIFF(StartDate, EndDate, DAY)
)
VAR WeekendDays =
IF(
ISBLANK(StartDate) || ISBLANK(EndDate),
BLANK(),
COUNTROWS(
FILTER(
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"DayOfWeek", WEEKDAY([Date], 2)
),
[DayOfWeek] > 5
)
)
)
VAR WeekendHours =
IF(
ISBLANK(WeekendDays),
BLANK(),
WeekendDays * 24
)
RETURN
IF(
ISBLANK(TotalHours) || ISBLANK(WeekendHours),
BLANK(),
TotalHours - WeekendHours
)

[d_rohlfs]

You should just be able to use the DateDiff function. I'm not positive how the blanks will work, but try both of these as measures:
Measure1 = DateDiff('Table'[Date],'Table'[Lead Date], HOUR)
Measure2 = IF(OR(ISBLANK('Table'[Date]),ISBLANK('Table'[Lead Date])), 0, DateDiff('Table'[Date],'Table'[Lead Date], HOUR))

If this answer was correct, consider marking it as a solution.
David Rohlfs MCDAA, CAPM
:: Welcome To EPM Strategy ::

[Kaviraj11]

Hi

Try this out (Calculated Column)

DifferenceInHours =

VAR StartDate = 'Table'[Date]

VAR EndDate = 'Table'[Lead Date]

VAR TotalHours = DATEDIFF(StartDate, EndDate, HOUR)

VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY)

VAR WeekendDays =

    COUNTROWS(

        FILTER(

            ADDCOLUMNS(

                CALENDAR(StartDate, EndDate),

                "DayOfWeek", WEEKDAY([Date], 2)

            ),

            [DayOfWeek] > 5

        )

    )

VAR WeekendHours = WeekendDays * 24

RETURN

TotalHours - WeekendHours

[durgaraop21]

Thank you for your message @Kaviraj11 .

I’ve tried the DAX code and explored various approaches, but I’m still encountering the following error: “The start date or end date in the Calendar function cannot be a blank value.”
Help me on this.