Calculate Date Durations between 2 dates

[icassiem]

Good day,

i would like to calculate the duration between 2 dates, so my visual will be per stage and its duration

1. Do I create a calculated column for duration between the 2 dates?

2. then a calculated measure to sum the duration and convert in days or hours

please help with the duration column & measure calculations

i hope the method of a calculated column then calculated measure is correct

please advise and help with calculation scripts

Regards

[Anonymous]

Hi @icassiem 

Please try this:

Table:

Then add 2 calculated columns:

duration_Day =
ABS (
    DATEDIFF ( 'Table'[HistoryClosedDateMax], 'Table'[HistoryCreatedDateMin], DAY )
)

duration_Hour =
ABS (
    DATEDIFF ( 'Table'[HistoryClosedDateMax], 'Table'[HistoryCreatedDateMin], HOUR )
)

And 2 Measures:

_duration_Day =
ABS (
    DATEDIFF (
        MAX ( 'Table'[HistoryClosedDateMax] ),
        MAX ( 'Table'[HistoryCreatedDateMin] ),
        DAY
    )
)

_duration_Hour =
ABS (
    DATEDIFF (
        MAX ( 'Table'[HistoryClosedDateMax] ),
        MAX ( 'Table'[HistoryCreatedDateMin] ),
        HOUR
    )
)

The result is as follow:

Here for your reference:

DATEDIFF function (DAX) - DAX | Microsoft Learn

ABS function (DAX) - DAX | Microsoft Learn

Best Regards

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

[rajendraongole1 · Accepted solution]

Hi @icassiem - your suggested method of using a calculated column for individual durations and a calculated measure for aggregation is correct.

using datediff. calculated column calculate duration days:

Duration_Days =
DATEDIFF(
TableName[StartDate],
TableName[EndDate],
DAY
)

create a measure for total duration as 

Total_Duration =
SUM(TableName[Duration_Days])

Convert Duration to Hours or Mixed Format

Duration_Format =
VAR Days = INT(TableName[Duration_Hours] / 24)
VAR Hours = MOD(TableName[Duration_Hours], 24)
RETURN
Days & " days " & Hours & " hours"

In your visual e.g., a table or matrix, add the "Stage" field to the rows.Add the Total_Duration measure to display the summed duration per stage. 

[icassiem]

Thank You both @rajendraongole1  and @Anonymous 

i used @rajendraongole1 approach, @Anonymous  as i was unsure of calculations having both max when i have in pivot visual slicing and dicing

I think i have it incorrect with the total and it needs to be an average time per stage or sub stage

How would I change the total duration to average if it is by opportunity, stage and sub stage, does it mean i have to calc the distinct opportuntity first "sales force data"?

please help

[icassiem]

ended up doing: AVERAGE(SalesForce_SalesStageVelocity[duration_Day])

i am now trying to place the average between 2 dates (created and modified)

DurationAVG_1 =

var _rangeStart = SalesForce_SalesStageVelocity[SalesStageVelocitySelectedStartDate]

var _rangeEnd = SalesForce_SalesStageVelocity[SalesStageVelocitySelectedEndDate]    

return

//if(

    //SELECTEDVALUE(SalesForce_SalesStageVelocity[HistoryCreatedDateMin])>=_rangeStart

    //&& SELECTEDVALUE(SalesForce_SalesStageVelocity[HistoryModifiedDateMax])>_rangeEnd

//,AVERAGE(SalesForce_SalesStageVelocity[duration_Day])

//,0)

CALCULATE(AVERAGE(SalesForce_SalesStageVelocity[duration_Day]),FILTER / SELECTEDVALUE(SalesForce_SalesStageVelocity[HistoryCreatedDateMin])>=_rangeStart )

please help, i am trying to filter then only avg on the filtered date range between the create and mod date

[icassiem]

Hi,

Any update on calculating the average between 2 date fields "from and to"?

Regards