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...
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

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

    • MFelix's avatar
      MFelix
      Super User
      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