Calculating Days and Hours between 2 DateTime columns excluding Saturday and Sunday

[prabhatnath]

Hello,

I have one Incidents table with columns as below:
IncidentId, Title, Severity, Owner, CreatedDateTime, Status, ResolveDateTime, AcknowledgeDateTime.

The CreatedDateTime, ResolveDateTime, and AcknowledgeDateTime columns are of dateTime data type.

I want help to have:
1) Calculate Age of the Incident in Days based on CreatedDateTime and Current Date Time.
2) Calculate Average Acknowledge time in Days and Hours based on AcknowledgeDateTime and CreatedDateTime
3) Calculate Average Resolve time in Days and Hours based on ResolveDateTime and CreatedDateTime

All above calculattion must be excluding Saturday and Sunday while calculating.

Thanks,
Prabhat

[Anonymous · Accepted solution]

Hi @prabhatnath ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the calculated column to calculate days.

Days = NETWORKDAYS('Table'[CreatedDateTime], TODAY()) - 1

3.Create the calculated column to calculate days and hours based on Acknowledge date and created date.

Acknowledge_aver = 
VAR days_ = NETWORKDAYS('Table'[CreatedDateTime], 'Table'[AcknowledgeDateTime]) - 1
VAR hour_ = HOUR('Table'[CreatedDateTime])
VAR end_hour = HOUR('Table'[AcknowledgeDateTime])

RETURN
IF(
        days_ > 0,
        (days_ - 1) * 24 + 24 - hour_ + end_hour,
        24-hour_
    )

//COUNTROWS(FILTER(CALENDAR('Table'[CreatedDateTime], 'Table'[AcknowledgeDateTime]), WEEKDAY([Date], 2) < 6))))))

4.Create the calculated column to calculate days and hours based on Resolve date and created date.

Resolve_aver = 
VAR days_ = NETWORKDAYS('Table'[CreatedDateTime], 'Table'[ResolveDateTime]) - 1
VAR hour_ = HOUR('Table'[CreatedDateTime])
VAR end_hour = HOUR('Table'[ResolveDateTime])

RETURN
IF(
        days_ > 0,
        (days_ - 1) * 24 + 24 - hour_ + end_hour,
        24 - hour_
    )

5.Drag the column into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[amitchandak]

@prabhatnath , For Networkdays , you can use networkdays function

Power BI - Business day with and without using DAX Function NETWORKDAYS: https://www.youtube.com/watch?v=Qs03ZZXXE_c
https://medium.com/@amitchandak/power-bi-dax-function-networkdays-5c8e4aca38c

For business hours use the blog from Matt -https://exceleratorbi.com.au/calculating-business-hours-using-dax/

[ryan_mayu]

could you pls provide some sample data and expected output?