Forum Discussion

villasenorbritt's avatar
villasenorbritt
Resolver I
4 years ago

Sorting data based off of Shift

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!

 

 

Need Help

5 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    4 years ago

    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's avatar
      villasenorbritt
      Resolver I
      4 years ago

      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's avatar
        rsbin
        Community Champion
        4 years ago

        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?