DAX Calculate duration when more than two dates are involved

[rustyrepublic]

Hello! 

After a good bit of digging, I can't find much on how I might accomplish calculating the duration (in days) between a range of date fields

I have multiple dates (dd/mm/yyyy) that tell me when a request exchanged hands between two groups and I'm trying to figure out how I calculate the total duration the request spends with each group.

For example. Total duration with group 1 = 

Duration between: Date Request Received & Date Request Sent out 

+

Duration between: Date Request Returned 1 & Date Request Sent out 2

+

Duration between: Date Request Returned 2 & Date Request Sent out 3  

Thanks for your help!

[v-eqin-msft]

Hi @rustyrepublic ,

Any update?

Best Regards,
Eyelyn Qin

[rustyrepublic]

Hi Evelyn! Thanks for your response, and my apologies for not replying sooner. I do appreciate your help and I think it could work. My only issue is that I have 6 pairs of dates that I am evaluating with DATESBETWEEN in order to get a total duration of time spent. 

I was wondering if I can use the expression you provided within DATESBETWEEN? Can DATESBETWEEN evaluate an IF statement?

OR could I define a variable to evaluate that IF statement and use the variable within DATESBETWEEN?

VAR newvariable = IF(ISBLANK('DATES2),TODAY(),'DATES2)
Return
IF (
    ISBLANK('DATE1),
    BLANK(),
CALCULATE(
    COUNTROWS( Dates ),
    DATESBETWEEN(
        Dates[Date],
        DATE1,
        newvariable+0),
        Dates[IsWorkingDay] = TRUE,
        ALLSELECTED('TABLE')
)
)

 Thanks,

Ross

[v-eqin-msft]

Hi @rustyrepublic ,

You could add a column firstly and then replace [DATE2] column with the new column in calculation 

New DATE2 = IF(ISBLANK([DATE2]),TODAY(),[DATE2])

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

[rustyrepublic]

Thanks for the response! I have come up with a work-around for this problem and I have another question related to the solution 

I am calculating duration as follows but I have one use case that is not covered with this method, and that is where date 2 is not available or blank

Duration = 
IF (
    ISBLANK('Date 1]),
    BLANK(),
CALCULATE(
    COUNTROWS( Dates ),
    DATESBETWEEN(
        Dates[Date],
        DATE1,
        DATE2 +0),
        Dates[IsWorkingDay] = TRUE,
        ALLSELECTED(TABLE)
)
)

. Is there a way of using today's day if date 2 is not available/blank in the calculation above? 

[amitchandak]

@rustyrepublic ,

One way is

diff = [Date Request Received] - [Date Request Sent out]

or

diff = datediff( [Date Request Sent out],[Date Request Received] ,day)

or

diff = datediff( [Date Request Sent out],[Date Request Received] ,second)

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

Dax do not have exact duration so you can convert diff in duration