cancel
Showing results for
Did you mean:
Highlighted
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.

1 ACCEPTED SOLUTION

Accepted Solutions
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!

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

Senior Member

## Re: Fraction of specific time between 2 datetime columns

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

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

Frequent Visitor

## Re: Fraction of specific time between 2 datetime columns

I tried your dax code but displays the following error

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.

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

## 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!

Announcements