locked
Time Intelligence for Hour Intervals? RRS feed

  • Question

  • How do you do time intelligence on hourly level? I'm good with Time Intelligence functions for days, months, and years.. but how about hours? I can't find anything on that it seems.

    I separate the time and date tables and link both to respective date and hourly columns in the fact tables (though it seems to slow the model compared to having a surrogate key combining date and time in one calendar table)

    How do I do the previous hour for example? how do I do a DATEADD functionality for hour intervals that are located in a time table separate from the days table. I've tried things like below. I'm thankful for any guidance or pointing to any article.

    I get blanks or no value in the attempt below. I'm only filtering on the time table here and not the other date table. But still doesn't work for any hour.

    Demand T-1 (MWhs) :=
    CALCULATE (
    SUM ( Table[Value] ),
    FILTER ( DimTime, DimTime[Hour_12HR] = DimTime[Hour_12HR] - TIME ( 1, 0, 0 ) )
    )



    • Edited by SSfadi Tuesday, April 26, 2016 1:27 AM
    Monday, April 25, 2016 2:05 AM

Answers

  • Unfortunately, the built-in time intelligence functions work on dates only, not time.

    The reason your attempts don't return any results, is that FILTER expects a boolean expression. Yours is "DimTime[Hour] = DimTime[Hour] - <one hour>" which is obviously always false. You could change this to

    FILTER(ALL(DimTime), DimTime[Hour] >= MIN(DimTime[Hour]) - TIME(1,0,0) && DimTime[Hour] <= MAX(DimTime[Hour]) - TIME(1,0,0))

    This will not work when the time slot crosses the date boundary, though; but it will when you include the dates in your time table.

    • Marked as answer by SSfadi Wednesday, May 4, 2016 2:32 AM
    Monday, April 25, 2016 9:16 AM
    Answerer
  • The time intelligence functions are designed to work on dates only, and they will not work with a Date+Time table.

    Maybe you can do what you want without a separate Time table? With some clever filtering on the fact table and the Date table, it could work out. Or create a filter like the one I proposed with a constructed date/time value:

    MIN(DimDate[Date]) + MIN(DimTime[Time]) is the first time in your context, subtract one hour and split in date and time parts: from date/time to date using TRUNC(<datetime>), from date/time to time using <datetime> - TRUNC(<datetime>)

    • Marked as answer by SSfadi Wednesday, May 4, 2016 2:32 AM
    Monday, April 25, 2016 7:19 PM
    Answerer

All replies

  • Unfortunately, the built-in time intelligence functions work on dates only, not time.

    The reason your attempts don't return any results, is that FILTER expects a boolean expression. Yours is "DimTime[Hour] = DimTime[Hour] - <one hour>" which is obviously always false. You could change this to

    FILTER(ALL(DimTime), DimTime[Hour] >= MIN(DimTime[Hour]) - TIME(1,0,0) && DimTime[Hour] <= MAX(DimTime[Hour]) - TIME(1,0,0))

    This will not work when the time slot crosses the date boundary, though; but it will when you include the dates in your time table.

    • Marked as answer by SSfadi Wednesday, May 4, 2016 2:32 AM
    Monday, April 25, 2016 9:16 AM
    Answerer
  • Thank you very much.  Of course my condition is always false and it's really not a condition but some counter.  I will try that and report back. 

    On the issue of crossing date boundaries, seems like there's no point in having two tables of Date and Time? Adding a year's worth of days and months to the time table may not be good enough since I need to cross a year as well?

    Monday, April 25, 2016 10:44 AM
  • The time intelligence functions are designed to work on dates only, and they will not work with a Date+Time table.

    Maybe you can do what you want without a separate Time table? With some clever filtering on the fact table and the Date table, it could work out. Or create a filter like the one I proposed with a constructed date/time value:

    MIN(DimDate[Date]) + MIN(DimTime[Time]) is the first time in your context, subtract one hour and split in date and time parts: from date/time to date using TRUNC(<datetime>), from date/time to time using <datetime> - TRUNC(<datetime>)

    • Marked as answer by SSfadi Wednesday, May 4, 2016 2:32 AM
    Monday, April 25, 2016 7:19 PM
    Answerer
  • The formula is almost working great, and thank you for new suggestions.  I'm using one calendar table and subtracting an hour from a date time field in this format: 2/1/2016 1:00:00 AM.  It works fine crossing into days and months. But for some reason, the Hour-1 value is skipping every third hour per below (get blanks for hour 2, 5, and 8.)  When I do hour forward, the Hour+1 value skips the same but starts an hour prior, so in this example, it skips hour 1, 4, 7).  I played around with it trying different min/max combinations but still skips two hours at a time.  Any idea??

    CALCULATE (
        MAX ( Table[Value] ),
        FILTER (
            ALL ( Calendar ),
            Calendar[DateTime]
                >= MIN ( Calendar[DateTime] ) - TIME ( 1, 0, 0 )
                && Calendar[DateTime]
                    <= MAX ( Calendar[DateTime] ) - TIME ( 1, 0, 0 )
        )

    )

    - TIME(1,0,0)

    2/1/2016 21:00

    5,832

    6,138

    2/1/2016 22:00

    5,338

    5,832

    2/1/2016 23:00

    4,789

    2/2/2016 0:00

    4,400

    4,789

    2/2/2016 1:00

    3,722

    4,400

    2/2/2016 2:00

    3,473

    2/2/2016 3:00

    3,340

    3,473

    2/2/2016 4:00

    3,284

    3,340

    2/2/2016 5:00

    3,367

    2/2/2016 6:00

    3,729

    3,367

    2/2/2016 7:00

    4,422

    3,729

    2/2/2016 8:00

    4,806

    2/2/2016 9:00

    4,873

    4,806

    + TIME(1,0,0)

    2/1/2016 21:00

    5,832

    5,338

    2/1/2016 22:00

    5,338

    2/1/2016 23:00

    4,789

    4,400

    2/2/2016 0:00

    4,400

    3,722

    2/2/2016 1:00

    3,722

    2/2/2016 2:00

    3,473

    3,340

    2/2/2016 3:00

    3,340

    3,284

    2/2/2016 4:00

    3,284

    2/2/2016 5:00

    3,367

    3,729

    2/2/2016 6:00

    3,729

    4,422

    2/2/2016 7:00

    4,422

    2/2/2016 8:00

    4,806

    4,873

    2/2/2016 9:00

    4,873

    5,008



    • Edited by SSfadi Wednesday, April 27, 2016 10:59 AM
    Tuesday, April 26, 2016 1:25 AM