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 appoin...
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")))))))))

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.

       

       

      • atjt217's avatar
        atjt217
        Helper III
        4 years ago

        Could it has somthing to do with being in Direct query? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi atjt217 ,

       

      Try to remove the equal sign

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

       

       

      Best Regards,

      Stephen Tao

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • atjt217's avatar
        atjt217
        Helper III
        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")))))))))