Create time groups/shifts

[Coops_15]

I'm newer to power bi and am struggling with time data. I have a data set that includes the date and time of events in separate columns. I want to be able to see and compare the number of events by shift ( days - 6AM-1:59PM, afternoons 2:00pm-10:59pm, nights - 11pm to 5:59am). I've tried a couple other solutions on the forum, but I think they are not working because my date and time columns are separated. They don't need to be separated for my purposes, but I can't change the source data, so I have to solve for it on my end. Any help is appreciated.

 

[MarkLaf · Accepted solution]

You want to have at least two separate dimension tables, one for dates and another for time. See the following guidance if you are unfamiliar with dimension and fact tables: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Once you have your separate time dimension set up, you can add a Shift column (either in Power Query with M or in modeling with DAX) that will assign the shift according to your logic.

Once relationships are set up, you can then group/filter your fact/transactional data by shifts from your time dimesnsion. You would be able to do something like add Shifts to one axis of a visual and add SUM or some other aggregation of your fact to the other axis.

If you want more specific help on certain transformations, etc., please provide a clear description of your desired output and some dummy data pasted into a table that we can easily copy and paste into PBI through Enter Data.

[v-karpurapud]

Hello @Coops_15 
Hope you are doing well!

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hello @Coops_15 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hello @Coops_15 

Could you please confirm if your query have been resolved the solution provided by @collinsg & @MarkLaf ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[collinsg · Accepted solution]

Good day Coops_15,

One way of doing this is to calculate the hour of day of each "TIME OF Event" and use a look up list to find the shift for that hour.

For example, create the lookup list by adding a step called "Shifts" - each entry corresponds to an hour of the day.

Shifts = List.Repeat({"Night"},5) & List.Repeat({"Morning"},8) & List.Repeat({"Afternoon"},8) & List.Repeat({"Night"},3)

then add a step to add a column (replace "Previous Step" with the name of step which got you to the table you illustrated). In this step the hour of the timestamp acts as an index into the list. It picks out the shift name at that index.

AddShifts = Table.AddColumn(#"Previous Step", "Shift", each Shifts{Time.Hour([TIME OF Event])}, type text)

Here is some sample data and the complete code to allocated each time to a shift - you can create a blank query and paste this code in. The sample data looks like this,

The result looks like this

...and here is the code

let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjK0MjWwMjBQitWJVjIwsjKCcwwtrYyROYamcGUWQBZcxgimLBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"TIME OF Event" = _t]),
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"TIME OF Event", type time}}),
   Shifts = List.Repeat({"Night"},5) & List.Repeat({"Morning"},8) & List.Repeat({"Afternoon"},8) & List.Repeat({"Night"},3),
   AddShifts = Table.AddColumn(#"Changed Type", "Shift", each Shifts{Time.Hour([TIME OF Event])}, type text)
in
   AddShifts 

With this done you can load the data and count the rows, stratifying by the name of the shift.

Hope this helps

[MarkLaf · Accepted solution]

You want to have at least two separate dimension tables, one for dates and another for time. See the following guidance if you are unfamiliar with dimension and fact tables: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Once you have your separate time dimension set up, you can add a Shift column (either in Power Query with M or in modeling with DAX) that will assign the shift according to your logic.

Once relationships are set up, you can then group/filter your fact/transactional data by shifts from your time dimesnsion. You would be able to do something like add Shifts to one axis of a visual and add SUM or some other aggregation of your fact to the other axis.

If you want more specific help on certain transformations, etc., please provide a clear description of your desired output and some dummy data pasted into a table that we can easily copy and paste into PBI through Enter Data.