How to LOOKUP day name based on date+time column to make the "DAY" 6AM-5:59AM and not 12-12

[Philip0123]

Hi All,

Hoping there's a real simple solution out there, but I need to report on days from 6am to 6am as that's how the shifts run and production volumes are measure as such. So for my reports to make sense the days need to stretch from 6-6 and not 12-12, ie all data entries between date and time 27 Aug 6AM-28 Aug 5:59AM would be classified as Monday. However, using the "ADD COLUMN>DATE>DAY>NAME OF DAY" functionality when editing queries in BI desktop I obviously get a day name based on the date.

I hope my question is clear? Perhaps the table below is a better example:

Order	Malfunct. start	Start Malfn (T)	Day Name (using query or formula)	Day Name (required output)
100011158366	2018/01/29	06:47:00	1	1
100011160712	2018/01/29	22:33:57	1	1
100011162189	2018/01/29	22:39:00	1	1
100011160780	2018/01/30	00:02:56	2	1
100011162067	2018/01/30	00:32:46	2	1
100011162292	2018/01/30	02:39:13	2	1
100011162149	2018/01/30	03:00:00	2	1
100011162149	2018/01/30	5:59:00	2	1
100011162149	2018/01/30	6:00:00	2	2
100011162149	2018/01/30	13:00:00	2	2
100011162149	2018/01/30	23:59:00	2	2
100011162149	2018/01/31	12:00:00	3	2
100011162149	2018/01/31	5:59:00	3	2
100011162149	2018/02/01	6:00:00	4	3

[v-lili6-msft]

hi,@Philip0123

  After my research, you can do these follow my steps like below:

Step 1:

Continue to use the "ADD COLUMN>DATE>DAY>NAME OF DAY" functionality to add the column Day Name (using query or formula) then add column start time:

Start time = "6:00:00"

and change the data type to time.

Step 2:

change the data type of column Day Name (using query or formula) to Whole number and add the column 

Day Name = IF(Table1[Start Malfn (T)]<Table1[Start time],(Table1[Day Name (using query or formula)]-1),Table1[Day Name (using query or formula)])

Result:

Best Regards,

Lin

[Stachu · Accepted solution]

you will have to change the [Malfunct. start] to type date time and then you can use this syntax when you add new column - it takes the time of malfunciton time and subtracts 6h to get the result you wanted

Date.DayOfWeek(
[Malfunct. start] 
+ #duration(0,Time.Hour([#"Start Malfn (T)"]),Time.Minute([#"Start Malfn (T)"]),Time.Second([#"Start Malfn (T)"]))
- #duration(0,6,0,0)
,0)

PS the expected output for the

2018/01/31

12:00:00

is inconsistent with the description, it should be 3 as the resutl of the formula, correct?

[Philip0123]

---

@Stachu wrote:

you will have to change the [Malfunct. start] to type date time and then you can use this syntax when you add new column - it takes the time of malfunciton time and subtracts 6h to get the result you wanted

Date.DayOfWeek(
[Malfunct. start] 
+ #duration(0,Time.Hour([#"Start Malfn (T)"]),Time.Minute([#"Start Malfn (T)"]),Time.Second([#"Start Malfn (T)"]))
- #duration(0,6,0,0)
,0)

PS the expected output for the

2018/01/31

12:00:00

is inconsistent with the description, it should be 3 as the resutl of the formula, correct?

---

Awesome, this works like a bomb. 

Just a quick follow up, I'm not that familiar with the M language, how do I replace the output (1-7) with the actual day names? Using DAX I can simply use the switch function, but not sure with M. Sorry, I realise that wasn't part of the original  problem description.

Also, 00:00 on 31st is technically Wednesday, or output would be 3. But we include everything on Wednesday up until 06:00 in Tuesday's figures, so it should be 2, or Tuesday.