Forum Discussion

javirmerino's avatar
javirmerino
Icon for Helper III rankHelper III
6 years ago
Solved

Calculate if ReceivedTime Is In Working Hours

Hi All, I'm hoping that this is a quick fix and is something obvious i'm missing...

 

The below doesnt seem to be working correctly for me. There are no errors with syntax so my assumption is that there is an issue comparing the [ReceivedTime] field and the TIMEVALUE?

 

 

InWorkingHours = SWITCH( AND (SELECTEDVALUE ( 'SysAid Extract'[ReceivedTime] ) >= TIMEVALUE("09:00:00") , SELECTEDVALUE ( 'SysAid Extract'[ReceivedTime] ) < TIMEVALUE("17:00:00")), TRUE , FALSE )

 

 

 I've also tried amending the TIMEVALUE function to TIME, but am having the same issue. Each return is FALSE regardless of if the [ReceivedTime] is between 9 and 5.

 

Any ideas?

Help me
  • AlB's avatar
    AlB
    6 years ago

    Hi javirmerino 

    I'm just seeing that the switch is not constructed correctly. Check out hot it works here:

    Try this:

    InWorkingHours =
IF (
    AND (
        SELECTEDVALUE ( 'SysAid Extract'[ReceivedTime] ) >= TIMEVALUE ( "09:00:00" ),
        SELECTEDVALUE ( 'SysAid Extract'[ReceivedTime] ) < TIMEVALUE ( "17:00:00" )
    ),
    TRUE,
    FALSE
)

     

    For the pbix, you have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi javirmerino 

    Can you share a sample pbix that reproduces the issue? It would then be more feasible to see what might be going on. 

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

     

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi javirmerino 

        I'm just seeing that the switch is not constructed correctly. Check out hot it works here:

        Try this:

        InWorkingHours =
IF (
    AND (
        SELECTEDVALUE ( 'SysAid Extract'[ReceivedTime] ) >= TIMEVALUE ( "09:00:00" ),
        SELECTEDVALUE ( 'SysAid Extract'[ReceivedTime] ) < TIMEVALUE ( "17:00:00" )
    ),
    TRUE,
    FALSE
)

         

        For the pbix, you have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

         

        Please mark the question solved when done and consider giving kudos if posts are helpful.

        Cheers  Datanaut