Forum Discussion

RemiAnthonise's avatar
RemiAnthonise
Helper V
7 years ago
Solved

Use SWITCH with TIME() / time frames.

Hi guys,

 

I use this formula to calculate some timeframes.

 

Timeframe=
SWITCH(TRUE();
Patient[planned dismissal] > TIME(07;00;00) && Patient[planned dismissal] < TIME(15;00;00); TIME(15;00;00);
Patient[planned dismissal] > TIME(15;00;00) && Patient[planned dismissal] < TIME(23;00;00); TIME(23;00;00);
TIME(07;00;00))

 

It doesn't work, my outcome is always the 'else': 07:00. See image below + expected results. How do I handle this?

 

Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    7 years ago

    Hi RemiAnthonise ,

     

    The question is about the data although you see the hours only the data behind is date so you need to get onlythe time part of Patient[Gepland ontslagtijd aangepast] column, redo your calculation to:

     

     

    Tijdsblok ontslag =
VAR Hour_Part =
    TIME ( HOUR ( Patient[Gepland ontslagtijd aangepast] ); MINUTE ( Patient[Gepland ontslagtijd aangepast] ); SECOND ( Patient[Gepland ontslagtijd aangepast] ) )
RETURN
    SWITCH (
        TRUE ();
        Hour_Part > TIME ( 07; 00; 00 )
            && Hour_Part < TIME ( 15; 00; 00 ); TIME ( 15; 00; 00 );
        Hour_Part > TIME ( 15; 00; 00 )
            && Hour_Part < TIME ( 23; 00; 00 ); TIME ( 23; 00; 00 );
        TIME ( 07; 00; 00 )
    )

     

     

    Gives the result below:

    I'm not addin the PBIX since it tell me it was done on a previous version so if I send it out to you it will not open.

     

    Regards,

    MFelix

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi RemiAnthonise ,

     

    Is the planned dismissal column formatted as time or date? 

     

    In the test I have made it work correctly with your calculation.

     

    Regards,

    MFelix

    • RemiAnthonise's avatar
      RemiAnthonise
      Helper V
      7 years ago

      Hi MFelix ,

       

      Thanks for your reply. I see that in my formula

      Timeframe=
      SWITCH(TRUE();
      Patient[planned dismissal] > TIME(07;00;00) && Patient[planned dismissal] < TIME(15;00;00); TIME(15;00;00);
      Patient[planned dismissal] > TIME(15;00;00) && Patient[planned dismissal] < TIME(23;00;00); TIME(23;00;00);
      TIME(07;00;00))

       

      the colum Patient[planned dismissal] is based on a conditional column. 

      Patient[planned dismissal] =

       

      Planned dismissal= IF(Patient[Gepland Ontslagdatum - Copy] = DATE(1900;1;1); NOW(); Patient[planned datetime])

       

      In the image below, you can see the other columns. I've set the column Planned dismissal to 'time'.

      So, to summarize: Timeframe is based on a calculated and a conditional column, changed to datatype 'time'. 

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi RemiAnthonise ,

         

        Is it possible to share a sample file?

         

        As I refer based on my tests everything should match.

         

        Regards,

        MFelix