Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yios
Helper I
Helper I

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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
ChrisMendoza
Resident Rockstar
Resident Rockstar

@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
    )
)





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.

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?

Cmcmahan
Resident Rockstar
Resident Rockstar

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!

 

I tried your dax code but displays the following error

Dax Error.JPG

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.

Cmcmahan
Resident Rockstar
Resident Rockstar

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.