cancel
Showing results for
Did you mean:
Regular Visitor

## Need Help with weekday hours calculation

Hi,

I have the following DAX calcuation to calculate the number of weekdays it takes from when something is submitted and a status is updated:

Response Time = IF(ISBLANK('Response Table'[Status Date])||ISBLANK('Response Table'[Submitted Date]),BLANK(),SWITCH (
TRUE (),
'Response Table'[Submitted Date] <'Response Table'[Status Date] , CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],'Response Table'[Submitted Date],'Response Table'[Status Date] )),
'Response Table'[Submitted Date] > 'Response Table'[Status Date],CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],'Response Table'[Status Date],'Response Table'[Submitted Date] )) * -1,
0
))

This is the calculaton I have for the referenced ISWorkDay in my Dates table

IsWorkDay = SWITCH(WEEKDAY('Date'[Date]),1,0,7,0,1)

Both of these are working great calculating the response time in weekdays.  I need to get the same calculation for hours instead of days. and have not been able to get that to work.

Any help is appreciated.

Thanks.

Judie

3 REPLIES 3
Super User

## Re: Need Help with weekday hours calculation

See if my Net Work Duration works for you:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration/m-p/481543

Also, my Net Work Days might interest you as well:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

Proud to be a Datanaut!

Community Support Team

## Re: Need Help with weekday hours calculation

This is the calculaton I have for the referenced ISWorkDay in my Dates table

IsWorkDay = SWITCH(WEEKDAY('Date'[Date]),1,0,7,0,1)

Both of these are working great calculating the response time in weekdays.  I need to get the same calculation for hours instead of days. and have not been able to get that to work.

If the "calculation for hours" means you want to achieve the workday hours, suppose everyday work hour is 8h, simply multiply 8 in after the calculate statement.

```Response Time =
IF (
ISBLANK ( 'Response Table'[Status Date] )
|| ISBLANK ( 'Response Table'[Submitted Date] ),
BLANK (),
SWITCH (
TRUE (),
'Response Table'[Submitted Date] < 'Response Table'[Status Date], CALCULATE (
SUM ( 'Date'[IsWorkDay] ),
DATESBETWEEN (
'Date'[Date],
'Response Table'[Submitted Date],
'Response Table'[Status Date]
)
)
* 8,
'Response Table'[Submitted Date] > 'Response Table'[Status Date], CALCULATE (
SUM ( 'Date'[IsWorkDay] ),
DATESBETWEEN (
'Date'[Date],
'Response Table'[Status Date],
'Response Table'[Submitted Date]
)
)
* -8,
0
)
)
```

If this is not you want, please share more details about requirement.

Community Support Team _ Jimmy Tao

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

Community Support Team