Morning, Noon and Night shift

[Anonymous]

I have datetime column that start from 01-01-2022 07:30:00 and end at 03-01-2022 23:59:59

I want to split days into Morning, Noon and Night shift

Morning => 07:00:00 - 15:00:00

Noon => 15:00:00 - 23:00:00

Night =>  23:00:00 - 07:00:00

( Night shift falls 7 hours in the next day)

In de powerbi file I show how I split days. I also created a date picker with the Before date en made a measure for the visual to filter the day chosen in the date picker.

The problem I couldn't solve is that Night shift falls 7 hours out of the chosen day So when I choose day 01-01-2022 and select Night shift I want to see the following results in the visual :

Values between 01-01-2022 23:00:00 and 02-01-2022 07:00:00

What powerbi gives back is :

Values between 01-01-2022 00:00:00 and 01-01-2022 07:00:00 &

values between 01-01-2022 23:00:00 and 01-01-2022 00:00:00 

How to solve this ? Thanks in advance

https://drive.google.com/file/d/1FK3aCiaDhk2kLYffl2lJztlSMc18h8P1/view?usp=sharing

[CNENFRNL · Accepted solution]

Similar to separating fiscal year, truncate "Shift Date" at 7:00 AM every morning,