Previous Hour and Next Hour in Time Range

[mish_1703]

Hi,

In my Fact table, I have two columns [FactTableLoad Date] and [FactTableLoad Time]

Using a DAX function, I would like to calculate what is the time within an hour ahead and an hour next of the current load time. 

So, if current load time is 11:00 AM, the outputs should be PrevHour = 10:00 AM and NextHour = 12:00 PM.

I have the code currently, but would like to optimize it using DATEADD() or any other appropriate DATE function.

VAR CurrLoadTime = TIMEVALUE(FactTableLoadTime)

Var PrevLoadDate =

IF (

CurrLoadTime > TIME (00, 00, 00) && CurrLoadTime < TIME ( 01, 00, 00 ),

FactTableLoadDate - 1,

FactTableLoadDate

)

VAR PrevHour =

IF (

CurrLoadTime > TIME (00, 00, 00 ) && CurrLoadTime < TIME ( 01,00, 00 ),

TIME (23, MINUTE ( CurrLoadTime ), SECOND ( CurrLoadTime ) ),

CurrLoadTime - TIME ( 1, 0, 0 )

)

Var NextLoadDate =

IF (

CurrLoadTime > TIME ( 23, 59, 00 ) && CurrLoadTime < TIME ( 00, 59, 00 ),

FactTableLoadDate + 1,

FactTableLoadDate

)

VAR NextHour =

IF (

CurrLoadTime > TIME ( 23, 59, 00 ) && CurrLoadTime < TIME ( 00,59, 00 ),

TIME ( 00, MINUTE ( CurrLoadTime ), SECOND ( CurrLoadTime ) ),

CurrLoadTime + TIME ( 1, 0, 0 )

)

RETURN

“PreviousHour= ”&PrevHour&”NextHour= ”&NextHour

[DataInsights]

@mish_1703,

You could create a time dimension table, with columns PrevHour and NextHour. Then, create a relationship with the fact table. This should be more efficient than calculated columns in a fact table.