Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date and Time

Hello All,

Please bear with me, this is my first time ever using forums and I'm new to Power bi. I'm creating a report for my team that needs to display 2 different shifts. A clickable slider that can show shift 1 or 2. Shift 1 is 6am - 6pm and shift 2 is from 6pm to 6am(through the next day). The problem I'm having is that when I filter a shift it shows multiple dates, so I added the relative date to show todays date because that’s all we need to see displayed. However, this works for shift 1, but shift 2 goes over night (yesterday's date) and through to the morning of (today's date.) Which means I need 6pm of yesterday day through midnight to the morning of the next day. But, when I filter today and yesterday then the data gives me the morning shift of yesterday as well as the night shift which is to many values. I'm not sure if what I said makes much sense but I'll add screenshots to help explain what I'm saying. 

 

 

Shift 2 starts at 6pm so I need the first line of data  to show 11/15/2022 around the time 6pm

 

 

When I add last 2 days in relative date it shows 12am of yesterday for shift 2 which makes sense but how would I make the range from 6pm(yesterday) - 6am(Today)?

 

 

Any type of help would be greatly appreciated!

    

 

 

Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi Anonymous ,

     

    Instead of using the original DateTime column, create a calculated column that  will move Shift 2 to another date if it is before 12AM.

     

    New Date = 
//6pm  in decimal
VAR _6pm = 0.75
RETURN
    IF (
        'Table'[Shift] = 2
            && ( 'Table'[DateTime] - INT ( 'Table'[DateTime] ) ) >= _6pm,
        INT ( 'Table'[DateTime] ) + 1,
        INT ( 'Table'[DateTime] )
    )

     

    The formula  will not yield the desired result if someone from Shift 2 works all the way to 6pm the next day.

     

2 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi Anonymous ,

     

    Instead of using the original DateTime column, create a calculated column that  will move Shift 2 to another date if it is before 12AM.

     

    New Date = 
//6pm  in decimal
VAR _6pm = 0.75
RETURN
    IF (
        'Table'[Shift] = 2
            && ( 'Table'[DateTime] - INT ( 'Table'[DateTime] ) ) >= _6pm,
        INT ( 'Table'[DateTime] ) + 1,
        INT ( 'Table'[DateTime] )
    )

     

    The formula  will not yield the desired result if someone from Shift 2 works all the way to 6pm the next day.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello danextian 
      It works!!Thank you so much!! I greatly appreciat it.