Need Help with weekday hours calculation

[jb123]

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

[v-yuta-msft]

@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

[v-yuta-msft]

@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.

[Greg_Deckler]

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