Sorting data based off of Shift

[villasenorbritt]

I have  two shifts that I am showing data for. I am sorting based off of the shift date and 1st shift looks fine since it runs from 5 AM to 5 PM. But, the issue is 2nd shift which runs from 5 PM to 5 AM the next day. When I sort based off shift date, and then drill down to look into the data hour by hour, it does this:

This is the formulas I used to group the data by time:

60Min_Data = INT(DATEDIFF(DATE(1899,12,30),vwDowntime[timeonly],MINUTE) / 60)

60Min_Time = TIME( INT([60Min_Data]*60/60), (( ([60Min_Data]*60/60)-INT([60Min_Data]*60/60))*60) ,0)

I would like 2nd shift to start at 5 PM and go in order, but Power Bi wants to sort it with the morning hours first. Basically, I need 2nd shift to show the PM values before the AM values. How can I fix this? Any suggestions greatly appreciated!

[rsbin]

@villasenorbritt ,

The way I handle this scenario is by creating a Dim_Time table consisting of the Hours of the Day and a SortOrder.

Based on your schedules, I believe your SortOrder looks something like this:

Time               TimeSort

5:00:00 AM	1
6:00:00 AM	2
7:00:00 AM	3
8:00:00 AM	4
9:00:00 AM	5
10:00:00 AM	6
11:00:00 AM	7
12:00:00 PM	8
1:00:00 PM	9
2:00:00 PM	10
3:00:00 PM	11
4:00:00 PM	12
5:00:00 PM	13
6:00:00 PM	14
7:00:00 PM	15
8:00:00 PM	16
9:00:00 PM	17
10:00:00 PM	18
11:00:00 PM	19
12:00:00 AM	20
1:00:00 AM	21
2:00:00 AM	22
3:00:00 AM	23
4:00:00 AM	24

After creating this table, use the SortByColumn tool in the Toolbar and you should be able to get your Hours sorted correctly.

Good Luck and Regards,

[villasenorbritt]

@rsbin I made the table. 

I tried connecting on just time, and also on the 60min_time since that is what is used once you drill down, but neither fixed my issue

Any suggestions as to where I am going wrong? 

[rsbin]

@villasenorbritt ,

Good Morning.  If I understood your original requirement correctly, you want 5:00 am to be SortOrder #1 and 5:00 pm to be SortOrder #13.  Based on your picture above, you have reversed the order.

Does this help?  Or have I misunderstood your requirement?

[villasenorbritt]

@rsbin I need 1st shift to have a different sort order then 2nd shift. 1st shift needs to go in the normal order of AM to PM while 2nd shift needs to go from PM to AM. Is there a way to get a different sort order for them? I was attempting to do the sort order for 2nd shift, which would start at 5 PM as sortorder 1 since that is when 2nd shift starts. But, it keeps it in the same order as before. 

[rsbin]

@villasenorbritt ,

Are you able to attach a sample pbix file with some actual data?

If not, please paste a sample of the table containing your 60_Min time.

I am assuming the Matrix picture above is coming from this table.