Forum Discussion

markefrody's avatar
markefrody
Icon for Post Patron rankPost Patron
5 years ago
Solved

Shift Categories Based on Date and Time

Hi,

 

I have a list of date and time per item. I need to add a calculated column wherein if a time falls between the below ranges they should be classified as follows:
A.) 4:00 AM - 3:59 PM

B,) 4:00 PM - 3:59 AM

Would you be able to help me with a DAX code for this calculated field? Below is an example of how it should like (Column D).


I am using Direct Query for the data.

Any help is greatly appreciated. Thanks!

 

Best regards, 
Mark V

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This calculated column formula will work

    =if(and(mod(Data[Creation date],1)>=time(4,0,0),mod(Data[Creation date],1)<=time(15,59,0)),"4 AM - 3:59 PM","4 PM - 3:59 AM")

    Hope this helps.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    This calculated column formula will work

    =if(and(mod(Data[Creation date],1)>=time(4,0,0),mod(Data[Creation date],1)<=time(15,59,0)),"4 AM - 3:59 PM","4 PM - 3:59 AM")

    Hope this helps.