topic Re: Datediff but excluding if there is a pause in work time in DAX Commands and Tips

Datediff but excluding if there is a pause in work time

lynnzrae — Tue, 04 Mar 2025 12:32:59 GMT

I would like to calculate the amount of time between Submitted for Review and a Decision being made. However, there are times when there is a pause of work and I want to exclude that time; but the pause doesn't always occur. Can someone please help me create a DAX exploring:

Datediff (in days) between submitted for review and decision rendered excluding the time between project pause and project restart (if there is a pause)

Re: Datediff but excluding if there is a pause in work time

bhanu_gautam — Tue, 04 Mar 2025 13:02:11 GMT

lynnzrae Ensure you have columns for "Submitted for Review", "Decision Rendered", "Project Pause", and "Project Restart".

Create a calculated column to determine the total number of days the project was paused.

Subtract the total pause duration from the overall duration between "Submitted for Review" and "Decision Rendered".

DAX
-- Step 1: Calculate the total pause duration
TotalPauseDuration =
SUMX(
FILTER(
YourTable,
NOT(ISBLANK(YourTable[Project Pause]) && ISBLANK(YourTable[Project Restart]))
),
DATEDIFF(YourTable[Project Pause], YourTable[Project Restart], DAY)
)

-- Step 2: Calculate the net duration excluding pauses
NetDuration =
DATEDIFF(YourTable[Submitted for Review], YourTable[Decision Rendered], DAY) - YourTable[TotalPauseDuration]

For more details share sample data and exact required data

Re: Datediff but excluding if there is a pause in work time

lynnzrae — Tue, 04 Mar 2025 13:26:37 GMT

I will work on this. thank you. It just dawned on me that we also don't include weekends and federal holidays. Is it possible to somehow pull that in to the caluclation as well?

Thank you

Re: Datediff but excluding if there is a pause in work time

lynnzrae — Tue, 04 Mar 2025 13:39:48 GMT

I ceated the below and received an error message: Too Many arguments were passed to the sum function. The maximum argument count for the funtion is 1. Can you please advise on where I went wrong? Thanks.

Total Period of Paused Review =

SUM(

FILTER(

TPermitBRGCycle,

NOT(ISBLANK(TPermitBRGCycle[CG-BRG-2 Paused; Start Date])&&ISBLANK(TPermitBRGCycle[CG-BRG-2 Restart; Complete Date]))

DATEDIFF(TPermitBRGCycle[CG-BRG-2 Paused; Start Date],TPermitBRGCycle[CG-BRG-2 Restart; Complete Date], DAY)

)