Dates - Shift times

[kaushik_asv]

Hello all, 

We run 2 shifts each day. Blue shift from 6.00 AM - 4.59 PM and Red shift runs from 5.00 PM - 3.59 AM. 

Looking to make a bar graph with Daily output on Y-axis on and Date field on X axis with shift as a legend.

I ran into a problem here. Ideally the data for Aug 31 is showing Blue shift without issues. Red shift crosses over between 2 dates. issue here is, I only see a small part of red shift data for that day. The rest of that data is carried over to September 1st. Also, for Aug 31, I will be seeing data from red shift of Aug 30th. 

Is there a way I could create a column to reflect the correct totals?

Thanks in advance.

[edhans · Accepted solution]

The following formula in a new column @kaushik_asv will look at the time and if it is before 4am (3:59:59 am or earlier) will roll it back a day.

if DateTime.Time([End Shift]) < #time(4,0,0) then 
Date.AddDays(DateTime.Date([End Shift]), -1)
else DateTime.Date([End Shift])

If you need more help, please provide sample data per the links below, and expected results.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum