Forum Discussion

atjt217's avatar
atjt217
Helper III
4 years ago
Solved

Label status based on Time

Hello, 

Would like to ask your help please? I have a table list of Appointment Dates and Times and I want to put a status to either Regular hours or After hours. It will be After hours if the appointment time is before 8am and on or after 5pm. Here is an example of what im trying to do: 

 

AppointmentIdDateTimeStatus
10446159/26/202112:30 PMRegular Hours
10516669/27/20215:00 PMAfter Hours
10544269/30/20213:05 PMRegular Hours
10544279/30/20213:05 AMAfter Hours
10553349/27/202110:00 AMRegular Hours
10675899/27/20218:15 AMRegular Hours
107467310/2/20219:50 AMRegular Hours
10854129/26/20211:45 PMRegular Hours
109028010/1/20217:45 AMAfter Hours
10902939/29/20212:00 PMRegular Hours
109119110/1/20212:30 PMRegular Hours
10992779/28/20215:00 PMAfter Hours
10992909/29/202110:20 AMRegular Hours
10992969/29/202110:18 AMRegular Hours

 

Please note im using direct query. I appreciate your advice. Thank you!

Need Help
  • atjt217's avatar
    atjt217
    4 years ago

    I found a work around on this issue and wanted to share to everyone. Instead of using TIME i extracted the Hours on the Date column using Power Query and used this calculated column formula:

    Appointment Status = IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 8, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 9, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 10, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 11, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 12, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 13, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour]= 14, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 15, "Regular Hours",

    IF(vu_Bi_UnableToFill_2019ToCurrent[Hour] = 16, "Regular Hours",

    "After Hours")))))))))

8 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    atjt217  calculated column

     

     

    Column = if(TIME(08,00,00)<='Fact'[Time]&&'Fact'[Time]<=TIME(17,00,00),"reg","after")

     

     

     

    and in case , if this needs to be a measure

     

    Measure = if(TIME(08,00,00)<=CALCULATE(MAX('Fact'[Time]))&&CALCULATE(MAX('Fact'[Time]))<=TIME(17,00,00),"reg","after")

     

     

     

    • atjt217's avatar
      atjt217
      Helper III
      4 years ago

      Hi, Thank you for responding back.

      I tried both but its not working. The formula is used was: 

      Status = if(TIME(08,00,00)<=vu_Bi_UnableToFill_2019ToCurrent[Time] && vu_Bi_UnableToFill_2019ToCurrent[Time]<=TIME(17,00,00),"reg","after")

       

       

      Is there anything i missed? Please let me know

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        atjt217  this is what I see. it is the same measure as previous one.