Forum Discussion
Advocate IIICalculating Days and Hours between 2 DateTime columns excluding Saturday and Sunday
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
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()) - 13.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.
3 Replies
- amitchandak
Super User
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-5c8e4aca38cFor business hours use the blog from Matt -https://exceleratorbi.com.au/calculating-business-hours-using-dax/
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()) - 13.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.
ryan_mayucould you pls provide some sample data and expected output?
