Forum Discussion

Post Prodigy

1 year ago

Solved

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

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.

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: