cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jb123 Regular Visitor
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
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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Need Help with weekday hours calculation

@jb123,

 


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
Community Support Team

Re: Need Help with weekday hours calculation

@jb123,

 

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

 

Regards,

Jimmy Tao