Time Period with stipulations

[Anonymous]

I am currently stuck, and I do not know how to even start to write a formula. I have a table that is like a tree. I have one day with three different times, which has a department for each time, which then has a shift for each time. Kind of like this,

Department	Date/Time	Shift	Data
Fork Lift	1/19/2023 15:01	1st Shift	…
		2nd Shift	…
		3rd Shift	…
	1/19/2023 7:03	1st Shift	…
		2nd Shift()	…
		3rd Shift	…
	1/18/2023 23:01	1st Shift()	…
		2nd Shift()	…
		3rd Shift	…
	1/18/2023 15:01	1st Shift	…
		2nd Shift	…
		3rd Shift()	…
Press	1/19/2023 15:01	1st Shift	…
		2nd Shift	…
		3rd Shift	…
	1/19/2023 7:03	1st Shift	…
		2nd Shift()	…
		3rd Shift	…
	1/18/2023 23:01	1st Shift()	…
		2nd Shift()	…
		3rd Shift	…
	1/18/2023 15:01	1st Shift	…
		2nd Shift	…
		3rd Shift()	…
Data	…	…	…

 I receive a report every 8 hours, 30 minutes after the previous shift. I will only have up to four dates/times because power query removes everything but my four newest reports. I need to show the three previous shifts for each department. However, the main problem is in the time the report is pulled. Because the information pulls 30 minutes after each shift, it shows incomplete data from the current shift the report was pulled. The report time cannot change because the supervisors need time to fix any problems. Also, the report only pulls by "today" (0:00 - 24:59), not the past 24 hours.

I need power bi to figure out the current shift and pull the data from a previous report. I have put a few cells in bold, and a few have parentheses at the end to try to clarify what I need. Every cell with parentheses will need to be used to pull data for the report's current shift. Every shift in bold is the current shift the report has pulled that need to use data from the previous shift.

Maybe I am just complicating things, but I cannot figure out how to make power bi do this for me. Can anyone help?

[Anonymous]

Good Afternoon @lbendlin ,

I hope you are having a blessed day so far. Here is a small sample of my data.

Date modified	Transform File.Department Name	Transform File.Shift Name	Transform File.REG 7U01	Transform File.OT 7U05 Hours	Transform File.Double time	Transform File.Arrived Early	Transform File.Arrived Early_1	Transform File.Tardy
1/23/2023 7:01	Assembly Fork Lift	1st Shift	0:00	0:00	0:00	7	1:15	0
1/23/2023 7:01	Assembly Fork Lift	3rd Shift	24:00:00	0:00	0:00	3	0:36	0
1/23/2023 7:01	Assembly Fork Lift	1st Shift	0:00	0:00	0:00	7	1:15	0
1/23/2023 7:01	Assembly Fork Lift	3rd Shift	20:15	15:45	0:00	4	1:18	0
1/21/2023 23:01	Assembly Fork Lift	1st Shift	0:00	62:00:00	0:00	7	1:00	0
1/21/2023 23:01	Assembly Fork Lift	2nd Shift	8:00	17:30	0:00	3	0:56	0
1/21/2023 23:01	Assembly Fork Lift	3rd Shift	20:15	23:45	0:00	5	1:34	0
1/21/2023 15:01	Assembly Fork Lift	1st Shift	0:00	62:00:00	0:00	7	1:00	0
1/21/2023 15:01	Assembly Fork Lift	2nd Shift	0:00	0:00	0:00	1	0:16	0
1/21/2023 15:01	Assembly Fork Lift	3rd Shift	20:15	15:45	0:00	4	1:18	0
1/20/2023 23:01	Assembly Fork Lift	1st Shift	19:15	53:00:00	0:00	6	7:10	0
1/20/2023 23:01	Assembly Fork Lift	2nd Shift	16:00	0:00	0:00	5	1:09	1
1/20/2023 23:01	Assembly Fork Lift	3rd Shift	19:45	4:15	0:00	4	0:56	0
1/20/2023 15:01	Assembly Fork Lift	1st Shift	1:00	27:15:00	0:00	5	5:05	0
1/20/2023 15:01	Assembly Fork Lift	2nd Shift	0:00	0:00	0:00	4	1:05	0
1/20/2023 15:01	Assembly Fork Lift	3rd Shift	19:45	4:15	0:00	4	0:56	0

checked 1/21/2023 7:01

1/20/2023 15:01	Assembly Fork Lift	1st Shift	1:00	27:15:00	0:00	5	5:05	0
1/20/2023 23:01	Assembly Fork Lift	2nd Shift	16:00	0:00	0:00	5	1:09	1
1/21/2023 7:01	Assembly Fork Lift	3rd Shift	0:00	0:00	0:00	0	0:00	0

checked: 1/21/2023 15:01

1/21/2023 15:01	Assembly Fork Lift	1st Shift	0:00	62:00:00	0:00	7	1:00	0
1/20/2023 23:01	Assembly Fork Lift	2nd Shift	16:00	0:00	0:00	5	1:09	1
1/21/2023 7:01	Assembly Fork Lift	3rd Shift	0:00	0:00	0:00	0	0:00	0

Checked: 1/21/2023 23:01

1/21/2023 15:01	Assembly Fork Lift	1st Shift	0:00	62:00:00	0:00	7	1:00	0
1/21/2023 23:01	Assembly Fork Lift	2nd Shift	8:00	17:30	0:00	3	0:56	0
1/21/2023 7:01	Assembly Fork Lift	3rd Shift	0:00	0:00	0:00	0	0:00	0

Checked: 1/22/2023 7:01

1/21/2023 15:01	Assembly Fork Lift	1st Shift	0:00	62:00:00	0:00	7	1:00	0
1/21/2023 23:01	Assembly Fork Lift	2nd Shift	8:00	17:30	0:00	3	0:56	0
1/22/2023 7:01	Assembly Fork Lift	3rd Shift	0:00	0:00	0:00	0	0:00	0

Checked: 1/22/2023 15:01

1/21/2023 15:01	Assembly Fork Lift	1st Shift	0:00	0:00	0:00	0	0:00	0
1/21/2023 23:01	Assembly Fork Lift	2nd Shift	8:00	17:30	0:00	3	0:56	0
1/22/2023 7:01	Assembly Fork Lift	3rd Shift	0:00	0:00	0:00	0	0:00	0

Checked: 1/22/2023 23:01

1/21/2023 15:01	Assembly Fork Lift	1st Shift	0:00	0:00	0:00	0	0:00	0
1/21/2023 23:01	Assembly Fork Lift	2nd Shift	0:00	0:00	0:00	0	0:00	0
1/22/2023 7:01	Assembly Fork Lift	3rd Shift	0:00	0:00	0:00	0	0:00	0

Checked: 1/23/2023 7:01

1/21/2023 15:01	Assembly Fork Lift	1st Shift	0:00	0:00	0:00	0	0:00	0
1/21/2023 23:01	Assembly Fork Lift	2nd Shift	0:00	0:00	0:00	0	0:00	0
1/23/2023 7:01	Assembly Fork Lift	3rd Shift	24:00:00	0:00	0:00	3	0:36	0

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523