define time values into shifts

[stuart_reeves]

Hi!

I am trying to define incident times into where they fall within a shift.

I have tried a few things but cannot get the formula right. I want all incidents that fall within the following times, to fall into their perspective shifts:

06:30:00 - 18:29:59   = Day

18:30:00 - 06:29:59   = Night

I've (pathetically) managed to display anything from 18:30-25:59 as Night using: 

Shift = IF('HART/HEMS data'[Response_Time] >= Time(18,30,00),"Night")

However, I cannot get my head around how to format this the way I want it.

Any help would be greatly appreciated 🙂

Thank you in advance.

[v-cazheng-msft · Accepted solution]

Hi @stuart_reeves 

You can create a Calculated column or Measure.

1 Calculated column

Shift column = IF('HART/HEMS data'[Response_Time] >= Time(18,30,00)||'HART/HEMS data'[Response_Time]<=TIME(6,29,59),"Night","Day")

2 Measure

Shift Meaasure = IF(SELECTEDVALUE('HART/HEMS data'[Response_Time]) >= Time(18,30,00)||SELECTEDVALUE('HART/HEMS data'[Response_Time])<=TIME(6,29,59),"Night","Day")

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stuart_reeves]

Hi @v-cazheng-msft 

That's brilliant, thank you! 

[Jihwan_Kim]

Hi, @stuart_reeves 

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thank you very much.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.