cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
yios Frequent Visitor
Frequent Visitor

Fraction of specific time between 2 datetime columns

Hi ,

I have below sample data

1st case

FromCol                             ToCol

08/29/2019 02:50 AM        08/29/2019 04:00 AM

 

2nd case

FromCol                             ToCol

08/29/2019 03:50 AM        08/29/2019 06:00 AM

 

3rd case

FromCol                             ToCol

08/29/2019 12:40 AM        08/29/2019 03:10 AM

 

I need Dax code in order to add a new calculated column which will display below logic

Find how many minutes exists between these 2 datetime columns that are included in time 01:00 am - 03:00 am

Time 01:00 am - 03:00 am is static 

For the 1st case it will be 10 minutes, for the 2nd case it will be 0 minutes and for the 3rd it will be 120 minutes.

 

Thnaks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Fraction of specific time between 2 datetime columns

I... don't think it should be connected your data table. I've never attempted this before, so this might be wrong, and take everything here with a grain of salt.  Again, DAX/Power BI do not handle time duration well, especially across multiple days.

 

I guess the first order of business is to describe your data a bit better.  In your data table, do you have one datetime column (that says Jan 1, 2019 12:01am) or a date column with a seperate time column?  Is it possible for a duration to go for 24+ hours and possibly cross through multiple 1am - 3am thresholds? What granularity does your time dimension go down to, the minute or the second?

Assuming the simplest setup, where you have timestamp and date columns separate and durations never go longer than 24 hours, the solution would work generally like this:

 

SimpleDuration =
VAR modifiedStart =
    IF (
        SELECTEDVALUE ( DataTable[StartTime] ) < SELECTEDVALUE ( DataTable[EndTime] ),
        SELECTEDVALUE ( DataTable[StartTime] ),
        TIME ( 0, 0, 0 )
    ) //If the start time is after the end time, assume that start time is from day before, and use midnight as new start time
RETURN
    CALCULATE (
        COUNTROWS ( dimTime ),
        FILTER (
            dimTime,
            dimTime[timestamp] >= modifiedStart
                && dimTime[timestamp] <= SELECTEDVALUE ( DataTable[EndTime] )
        ),
        FILTER ( dimTime, dimTime[Hour] >= 1 && dimTime[Hour] <= 3 )
    )

 

This will return the number of minutes/seconds (depending on granularity of time dimension) between 1 and 3 am.  You'll need to convert/format as appropriate.

I'm taking a shortcut here so we can ignore the date, where if the start time is after the end time (11:50p is after 5:00a) then assume the duration started on the previous day and use 12:00a as the start time instead of 11:50p.  This breaks if the duration covers over 24 hours or if the start time is before 3a the day before. e.g. 2:30a on Jan 1 to 1:30a on Jan 2 should give an answer of 1hr, but because 2:30a is after 1:30a, the shortcut logic changes start time to 12a and you get 30mins as the answer.

 

It's quick and it's dirty, but it gets the general form across.  You may have to play with it some, especially if you have the possiblity of a multi-day duration.  Let us know how it goes!

 

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Fraction of specific time between 2 datetime columns

Do you have a time dimension?  This calculation becomes trivial with a good time dimension.

https://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/

 

Even if you don't link it to dates, just listing out every minute of the day in a dimension table makes this calculation 100x easier.

 

ChrisMendoza Senior Member
Senior Member

Re: Fraction of specific time between 2 datetime columns

@yios -

I have never needed a time dimension as @Cmcmahan suggested however, after writing this formula I would definitely look into it if I needed it.

 

It's ugly:

Column =
IF (
    TIME ( HOUR ( TableName[FromCol] ), MINUTE ( TableName[FromCol] ), SECOND ( TableName[FromCol] ) )
        >= TIME ( 1, 0, 0 ),
    IF (
        TIME ( HOUR ( TableName[FromCol] ), MINUTE ( TableName[FromCol] ), SECOND ( TableName[FromCol] ) )
            >= TIME ( 3, 0, 0 ),
        0,
        DATEDIFF (
            TableName[FromCol],
            DATEVALUE (
                YEAR ( TableName[FromCol] ) & ","
                    & MONTH ( TableName[FromCol] ) & ","
                    & DAY ( TableName[FromCol] )
            ) & " "
                & TIMEVALUE ( 3 & ":" & 0 & ":" & 0 ),
            MINUTE
        )
    ),
    DATEDIFF (
        DATEVALUE (
            YEAR ( TableName[FromCol] ) & ","
                & MONTH ( TableName[FromCol] ) & ","
                & DAY ( TableName[FromCol] )
        ) & " "
            & TIMEVALUE ( 1 & ":" & 0 & ":" & 0 ),
        DATEVALUE (
            YEAR ( TableName[FromCol] ) & ","
                & MONTH ( TableName[FromCol] ) & ","
                & DAY ( TableName[FromCol] )
        ) & " "
            & TIMEVALUE ( 3 & ":" & 0 & ":" & 0 ),
        MINUTE
    )
)
Super User
Super User

Re: Fraction of specific time between 2 datetime columns

Yeah, it gets real ugly, fast.  Usually time dimensions aren't needed, but when doing analysis like this, they become much more useful. I'm also pretty sure this solution wouldn't give correct results if the time span is 11pm - 5am.   Or a time span that goes 24+ hours.  You're also always using FromCol, but not comparing it against the ToCol.

 

It might actually be more efficient to test whether 1am and 3am are between the start and end times, and then do 3-4 different calculations depending on that result.  If it doesn't include the 1-3 time then 0mins, else if start is before 1 and end is after 3 then 120 mins, else if start is before 1 and end is before 3, change the start to 1 and do the math, else if start is after 1 and end is after 3, change end to 3 and do the math.

 

I'm of the opinion that when it comes to time duration data, Power BI and DAX just aren't useful tools, since they lack the capability to handle durations cleanly.  It's usually better to calculate this stuff at the data source if possible.

yios Frequent Visitor
Frequent Visitor

Re: Fraction of specific time between 2 datetime columns

I tried your dax code but displays the following error

Dax Error.JPG

Super User
Super User

Re: Fraction of specific time between 2 datetime columns

That looks like you tried to pass it a row with a null date. So since there was no year/month/day, it tried to turn ",," into a date. You should be able to replace all of the DATEVALUE expressions with this to fix it:

DATE( YEAR(TableName[FromCol] ), MONTH ( TableName[FromCol] ), DAY ( TableName[FromCol] ))

I still think that a time dimension is likely the way to solve this.

yios Frequent Visitor
Frequent Visitor

Re: Fraction of specific time between 2 datetime columns

You are 100% right.

I had null values and using 

DATE( YEAR(TableName[FromCol] ), MONTH ( TableName[FromCol] ), DAY ( TableName[FromCol] ))

dax error disappeared.

 

Above code gives wrong results in many cases.

1) for time interval 11:50 pm - 05:00 am it returns 0 minutes, which is wrong.

2) for time interval 12:00 am - 12:30 am it returns 120 minutes, which is wrong.

3) for time interval 01:15 am - 02:00 am it returns 120 minutes, which is wrong.

 

Can you describe the technique that i should use in order to benefit from time dimension (I have already loaded it in my data model based on your referenced article).

Should i connect it or not? Dax code?

Super User
Super User

Re: Fraction of specific time between 2 datetime columns

I... don't think it should be connected your data table. I've never attempted this before, so this might be wrong, and take everything here with a grain of salt.  Again, DAX/Power BI do not handle time duration well, especially across multiple days.

 

I guess the first order of business is to describe your data a bit better.  In your data table, do you have one datetime column (that says Jan 1, 2019 12:01am) or a date column with a seperate time column?  Is it possible for a duration to go for 24+ hours and possibly cross through multiple 1am - 3am thresholds? What granularity does your time dimension go down to, the minute or the second?

Assuming the simplest setup, where you have timestamp and date columns separate and durations never go longer than 24 hours, the solution would work generally like this:

 

SimpleDuration =
VAR modifiedStart =
    IF (
        SELECTEDVALUE ( DataTable[StartTime] ) < SELECTEDVALUE ( DataTable[EndTime] ),
        SELECTEDVALUE ( DataTable[StartTime] ),
        TIME ( 0, 0, 0 )
    ) //If the start time is after the end time, assume that start time is from day before, and use midnight as new start time
RETURN
    CALCULATE (
        COUNTROWS ( dimTime ),
        FILTER (
            dimTime,
            dimTime[timestamp] >= modifiedStart
                && dimTime[timestamp] <= SELECTEDVALUE ( DataTable[EndTime] )
        ),
        FILTER ( dimTime, dimTime[Hour] >= 1 && dimTime[Hour] <= 3 )
    )

 

This will return the number of minutes/seconds (depending on granularity of time dimension) between 1 and 3 am.  You'll need to convert/format as appropriate.

I'm taking a shortcut here so we can ignore the date, where if the start time is after the end time (11:50p is after 5:00a) then assume the duration started on the previous day and use 12:00a as the start time instead of 11:50p.  This breaks if the duration covers over 24 hours or if the start time is before 3a the day before. e.g. 2:30a on Jan 1 to 1:30a on Jan 2 should give an answer of 1hr, but because 2:30a is after 1:30a, the shortcut logic changes start time to 12a and you get 30mins as the answer.

 

It's quick and it's dirty, but it gets the general form across.  You may have to play with it some, especially if you have the possiblity of a multi-day duration.  Let us know how it goes!

 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)