Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RemiAnthonise
Helper V
Helper V

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?

timeframe.jpg

 

1 ACCEPTED SOLUTION

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:

patient.png

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

timeframe datetime.jpg

Hi @RemiAnthonise ,

 

Is it possible to share a sample file?

 

As I refer based on my tests everything should match.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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:

patient.png

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks a lot for your help, @MFelix .

Hi @MFelix , here is my sample file.

Thanks in advance!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.