Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Time Format Boolean Column

Hi, 

 

I have created a calculated column to show if certain end time (format 09:20) is false between certain ranges.

 

DAX used

7AM-10am = IF(HOUR(dilo_master[end_time]) >= 7 && HOUR(dilo_master[end_time]) <= 10.00 ,TRUE(),FALSE())
 
I want this measure to state True if the time range is between 7:00am - 10:00am. This measure is still picking up end time values up to 10:59am.
 
Any ideas on how to cut this off at 10am?
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    I assumed your end time column was a Time column, but is it a DateTime?  If so, you'll have to get the time component from it like this.  You should consider splitting DateTime columns if not already.

     

    7AM-10am =
    VAR vTime =
        TIME ( HOUR ( dilo_master[end_time] ), MINUTE ( dilo_master[end_time] ), SECOND ( dilo_master[end_time] ) )
    RETURN
        IF (
            vTime >= TIME ( 7, 0, 0 )
                && vTime <= TIME ( 10, 0, 0 ),
            TRUE (),
            FALSE ()
        )

    Pat

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    Please try this instead

     

    7AM-10am = IF(dilo_master[end_time] >= TIME(7,0,0) && dilo_master[end_time] <= TIME(10,0,0) ,TRUE(),FALSE())

     

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This has returned FALSE for every row. I have tried changing my end time column format between long and short time but this has not changed the output.

       

      Thanks,

       

      Elliot 

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        4 years ago

        I assumed your end time column was a Time column, but is it a DateTime?  If so, you'll have to get the time component from it like this.  You should consider splitting DateTime columns if not already.

         

        7AM-10am =
        VAR vTime =
            TIME ( HOUR ( dilo_master[end_time] ), MINUTE ( dilo_master[end_time] ), SECOND ( dilo_master[end_time] ) )
        RETURN
            IF (
                vTime >= TIME ( 7, 0, 0 )
                    && vTime <= TIME ( 10, 0, 0 ),
                TRUE (),
                FALSE ()
            )

        Pat