cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DreDre
Helper I
Helper I

Dealing with an offset 24 hour clock

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

2 ACCEPTED SOLUTIONS
PaulDBrown
Super User
Super User

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:

Datechange.JPG

(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: 

dim time.JPG

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.

2021-09-25.png

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

Custom format.JPG

 

Dim time custom.JPG

 

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

result1.JPG

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:

Continuous.png

 I've attached a sample PBIX file

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

DreDre
Helper I
Helper I

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

View solution in original post

5 REPLIES 5
DreDre
Helper I
Helper I

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

View solution in original post

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






v-caitlyn-mstf
Community Support
Community Support

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
Super User
Super User

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:

Datechange.JPG

(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: 

dim time.JPG

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.

2021-09-25.png

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

Custom format.JPG

 

Dim time custom.JPG

 

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

result1.JPG

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:

Continuous.png

 I've attached a sample PBIX file

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

hnguy71
Solution Supplier
Solution Supplier

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!