cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KellyLen Regular Visitor
Regular Visitor

Creating durations

Hello,

 

I am having difficulties with creating durations. I have different date columns and I would like to calculate durations between them. 

For example three columns, which are in date/time format:

image.png

 

I would like to get durations, how much there has been time between scanning date and basic data changed time, basic data changed time and posting data changed time etc. 

I have tried to do this in Query Editor with simple formula basic data changed - scanning date, but this does not allow me to get data type duration and it cannot be used for making conclusions - averages, totals etc. I would like to get conclusions in days, hours and minutes. 

 

Kindly waiting for your help,

Kelly 

6 REPLIES 6
Super User
Super User

Re: Creating durations

@KellyLen

 

Hi... You can also use DAX... DATEDIFF function to compute duration

 

https://msdn.microsoft.com/en-us/library/dn802538.aspx

v-xjiin-msft Super Contributor
Super Contributor

Re: Creating durations

Hi @KellyLen,

 

To achieve your requirement, you can try Power Query functions. Please refer:

 

1. Duration.ToText() function:

 

1.PNG

 

2. Duration.ToRecord() function. And expand columns.

 

2.PNG

 

Thanks,
Xi Jin.

 

KellyLen Regular Visitor
Regular Visitor

Re: Creating durations

Hello,

 

Duration.ToText does not allow me to make conclusions afterwards in visuals. E.g. there is not possible to take average time or make sums based on some other column's values. 

Duration.ToRecord gives me too many separate columns, which are not even taking into consideration the previous columns- for example, if I choose to show only hours, then it does not convert the days or minutes into it. And if I have like 10 different durations, then I would have 10 different days columns, 10 different hours columns..

 

This seems so messy, is not there any better solution? :/

 

Thanks,

Kelly 

KellyLen Regular Visitor
Regular Visitor

Re: Creating durations

Hi,

 

This basically works, but allows me to present only one value in one column - the days or hours or minutes or etc. But if I would like to present days and hours and minutes ? 

 

Best regards,

Kelly 

KellyLen Regular Visitor
Regular Visitor

Re: Creating durations

Hi,

 

Now turned out that the DATEDIFF is not working for me as well. I have different dates in columns and some of the start date column values are after end dates, so the function is not working.. Smiley Sad 

 

Please help. Smiley Happy 

v-xjiin-msft Super Contributor
Super Contributor

Re: Creating durations

Hi @KellyLen,

 

With Duration.ToText, you can use following DAX expression to calculate the Average or Sum in measures:

 

SumDuration =
VAR TotalSeconds =
    SUMX (
        'Table4',
        HOUR ( 'Table4'[Duration] ) * 3600
            + MINUTE ( 'Table4'[Duration] ) * 60
            + SECOND ( 'Table4'[Duration] )
    )
VAR Days =
    TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
    TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
    TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
VAR Secs =
    MOD ( TotalSeconds, 60 )
RETURN
    IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
        & IF ( Hors < 10, "0" & Hors, Hors )
        & ":"
        & IF ( Mins < 10, "0" & Mins, Mins )
        & ":"
        & IF ( Secs < 10, "0" & Secs, Secs )
AvgDuration =
VAR TotalSeconds =
    AVERAGEX (
        'Table4',
        HOUR ( 'Table4'[Duration] ) * 3600
            + MINUTE ( 'Table4'[Duration] ) * 60
            + SECOND ( 'Table4'[Duration] )
    )
VAR Days =
    TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
    TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
    TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
VAR Secs =
    MOD ( TotalSeconds, 60 )
RETURN
    IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
        & IF ( Hors < 10, "0" & Hors, Hors )
        & ":"
        & IF ( Mins < 10, "0" & Mins, Mins )
        & ":"
        & IF ( Secs < 10, "0" & Secs, Secs )

With Duration.ToRecord, you can combine those columns to make them display in one column like:

 

Detail Duration =
Table4[DurationToRecord.Days] & " days "
    & Table4[DurationToRecord.Hours]
    & " hours "
    & Table4[DurationToRecord.Minutes]
    & " minutes "
    & Table4[DurationToRecord.Seconds]
    & " seconds "

1.PNG

 

Thanks,
Xi Jin.