Dealing with an offset 24 hour clock

[DreDre]

So I am pulling data from an ODBC where events are tracked based on local time in a 24 hour clock of 00:00 - 23:59. When we are evaluating a "Day" we refer to each day as 02:01 - 02:00. The problem I am running into is that any event between 00:00 - 02:00 is being counted on the next day, when we look at it for the day prior. 

I am hoping for some help with the following:

To offset the date for any event in that 2 hour window to be the day prior so that the start of the day is actually the end of the day on charts.

My first thought is in power query adding a custom column and using the below as the formula.

(DepLoc = Local time in the standard 24 hour clock.)

=if [DepLoc] < #time(02, 00, 00) then [DepLoc] - #time(24, 00, 00) else DepLoc

Take the DepLoc if it is less than 2am and subtract 24 hours from it so it shows up on the date we consider it on, otherwise show the DepLoc as is.

What am I doing wrong with my formula? This is erroring out

[PaulDBrown · Accepted solution]

In Power Query, try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcu7DcAgEIPhXa5GwmcSXqsg9l8jkKNACY1dfPpbE6hH8QRVnGiuwPyx3X3wMuNtxL1LZvFkNUxK2SjsRMuK/m0RcaS3HtQf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Value", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Time] < #time(2, 1, 0) then Date.AddDays([Date] as date, -1) else [Date]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "FactDate"}, {"Date", "SourceDate"}})
in
    #"Renamed Columns"

to get:

(You can in fact remove the original SourceDate column as the last step in the query. I've left it in for illustration purposes) 

To order Time so that 00:00 to 02:00 are set at the end of the day, create a dimension table for time including a column to sort the time by:

let
    Source = List.Times(#time(0, 0, 0), 1440, #duration(0, 0, 1, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type2", "Number", each let splitTime = Splitter.SplitTextByDelimiter(":", QuoteStyle.None)(Text.From([Time], "es-ES")) in Text.Combine({Text.Combine(splitTime), "00"}), type text),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Column",{{"Number", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "26 hour day", each if [Number] < 20001 then ([Number] + 240000)/100 else [Number]/100),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Number"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"26 hour day", Int64.Type}})
in
    #"Changed Type1"

And sort the Time column by the  26 hour day column once loaded in the model and you can use either in visuals: 

Then create a Date table, and join it in a one-to-many relationship with the newly created date field (FactDate in my example) and do the same between the Time fields in the dimension table and fact table. The model looks like this.

Next, in model view, select the 26 hour day column in the field list and set the format to custom using:

And here is an example of what you can get setting the axis as categorical:

The advantage of using the 26 hour day column is that IMHO it is clearer and that you can use it in a continuos axis, whereas if you use the Time column in a continuous axis, the default time order overides the order established by the "Order column by" function:

 I've attached a sample PBIX file

[DreDre · Accepted solution]

@hnguy71 You are right, I should have started there, but after a little more tweaking I was able to find the less desireable result where it will show at the start of the day by adjusting my formula to be :

=if Time.From([DepLoc]) < #time(02, 00, 00) then [DepLoc] - #duration(1,0,0,0) else [DepLoc]

(If the time in DepLoc was less thatn 2am then subtract 1 day of time otherwise show DepLoc as is)

My data in DepLoc looks like ( 1/25/2021 12:10:00 AM ) and is in the format of "Date/Time"

@PaulDBrown I am very interested in how you were able to accomplish this as I think that a 26 hour day would be a better user experience. Looking at the PBIX file you have the data starting as two columns, one with date and another with time. I think that I am going to play with this to see if I can use this same structure for my data. I really appreciate such a detailed answer! I can definitely see how this would also answer my question, Thank-you 🙂 

[PaulDBrown]

As regards the Time dimension, you can just copy and paste the code into a blank query (it is unrelated to any other queries, and just creates the table from scratch).

As regards your data being in Date/Time, it is generally recommended to split it into two columns (Date and Time columns) to enable the use of a Date table and a Time dimension (you can split the Date/Time column in Power Query of course). If you try to create a Date/Time dimension, each year will have  365 * 24 * 60 rows. A Date table has 365 rows per year and a Time Dimension is only 1440 rows (for hours and minutes). This makes the model much more efficient.

[v-xiaoyan-msft]

Hi @DreDre ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown · Accepted solution]

In Power Query, try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcu7DcAgEIPhXa5GwmcSXqsg9l8jkKNACY1dfPpbE6hH8QRVnGiuwPyx3X3wMuNtxL1LZvFkNUxK2SjsRMuK/m0RcaS3HtQf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Value", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Time] < #time(2, 1, 0) then Date.AddDays([Date] as date, -1) else [Date]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "FactDate"}, {"Date", "SourceDate"}})
in
    #"Renamed Columns"

to get:

(You can in fact remove the original SourceDate column as the last step in the query. I've left it in for illustration purposes) 

To order Time so that 00:00 to 02:00 are set at the end of the day, create a dimension table for time including a column to sort the time by:

let
    Source = List.Times(#time(0, 0, 0), 1440, #duration(0, 0, 1, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type2", "Number", each let splitTime = Splitter.SplitTextByDelimiter(":", QuoteStyle.None)(Text.From([Time], "es-ES")) in Text.Combine({Text.Combine(splitTime), "00"}), type text),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Column",{{"Number", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "26 hour day", each if [Number] < 20001 then ([Number] + 240000)/100 else [Number]/100),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Number"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"26 hour day", Int64.Type}})
in
    #"Changed Type1"

And sort the Time column by the  26 hour day column once loaded in the model and you can use either in visuals: 

Then create a Date table, and join it in a one-to-many relationship with the newly created date field (FactDate in my example) and do the same between the Time fields in the dimension table and fact table. The model looks like this.

Next, in model view, select the 26 hour day column in the field list and set the format to custom using:

And here is an example of what you can get setting the axis as categorical:

The advantage of using the 26 hour day column is that IMHO it is clearer and that you can use it in a continuos axis, whereas if you use the Time column in a continuous axis, the default time order overides the order established by the "Order column by" function:

 I've attached a sample PBIX file

[hnguy71]

can you post a sample of your data and expected results?