Calculate Date Durations between 2 dates

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

Need Help

rajendraongole1
1 year ago
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.

5 Replies

rajendraongole1
Super User
1 year ago
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
  Post Prodigy
  1 year ago
  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
    Post Prodigy
    1 year ago
    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

Anonymous

Not applicable

1 year ago

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: