Timestamp vs. Plan - how to "between" two dates, different row

[Chocohammer]

Hello fellow Power BI users,

I hope you all doing great!

I have come for help, because I really don't know how to progress with my project. I have been stuck to this problem for almoust two weeks now. I have very comlex table, which is appended 3 tables as such rawData, serviceRequest and ProductionPlan. I wanted to "kill two flies by one round" when I came to the problem with Shifts and ShiftsUsage. Basicly I can't tell ftom Timestamp if the time was during 8h shift or 12 hour shift. By comparing to the plan I should be able to tell, which one of those it was as well how many hours should be the working time for the ShiftUsage, which is telling me how much time the Operater actually worked of his Shift.

Sample there (very chopped) :

I can't share the file as it is because of firm rules about data, but if really needed, I can upload this excel later on.

The ShiftPlan is calculated like this:

ShiftPlan =

SWITCH (

    TRUE (),

    ((HOUR ( Data[start] ) = 4

        && HOUR ( Data[end] ) = 12 ) && Data[shiftHoursData] = 8 ) , "Morning Shift",

    ((HOUR ( Data[start] ) = 12

        && HOUR ( Data[end] ) = 20 ) && Data[shiftHoursData] = 8 ), "Afternoon Shift",

    ((HOUR ( Data[start] ) = 20

        || HOUR ( Data[end] ) = 4 ) && Data[shiftHoursData] = 8 ), "Night Shift",

    ((HOUR ( Data[start] ) = 4

        && HOUR ( Data[end] ) = 16 ) && Data[shiftHoursData] = 12 ), "12-Hour Morning Shift",

    ((HOUR ( Data[start] ) = 16

        || HOUR ( Data[end] ) = 4 ) && Data[shiftHoursData] = 12 ), "12-Hour Night Shift"

)

The main goal is DAX code that will provide Shift to the Timestamp row from the Production Plan, that way I can tell if the timestamp is from 8h or 12h shift and also to calculate Shift Usage. Any help will be aprisieded, because as I said, I'm in pinch. It may or it may not be a challenge or if it's even possible.

Thank you so much and have a great day!

Choco

[Chocohammer]

Hi , @JoeBarry 

I have one additional question. I found out that I need shifts even for services. I added conditions for that, but the code is so non optimal. It takes almost an hour to run, and refresh almost 2 hours. 

Do you think I have an error in the code, is it overloaded or does it need another approach?

I did this:

Shift =

MAXX (

    FILTER (

        poductionPlan_stations,

        'Data'[deviceID] = poductionPlan_stations[deviceID]

            && ('Data'[timestamp] >= poductionPlan_stations[start]

            && Data[timestamp] < poductionPlan_stations[end])

            || (Data[requestTime] >= poductionPlan_stations[start]

            && Data[requestTime] < poductionPlan_stations[end])

    ),

    poductionPlan_stations[ShiftPlan]

)

Thank you so much for any suggestions.

Best regards

Choco

[JoeBarry]

Hi @Chocohammer 

Is the plan for an individual person or is it a general shift plan for each day? Is it possible that an 8 or 12 hour shift overlap on the same times? Example 8pm - 4am or 8pm - 6am on the same dates?

If it's for an individual person is there a UserID in both the plan and the RawData table available?

let me know these tings and I will try to help you

Kind rgeards

Joe

[Chocohammer]

Hi @JoeBarry 

Thank you for your reply!

The plan is individual for every deviceID. I have more than 500 deviceID, every deviceID is an individual machine that operator works on.

So for your question, it's not possible for the plan to overlap, because one machine can't work multiple time at one. The only possible way is, when changes have been made at the Plan and the "Old" Plan is deleted (TRUE statement).

I hope I answerd your question right. I will gladly answer more.

Best regards
Choco

[JoeBarry · Accepted solution]

Hi @Chocohammer 

For your plan, if a plan is deleted, then filter the table is FALSE in Power Query. This will show the real Plan

I presume your Plan table has a deviceID column? 

Load the Plan and the Actual table into the Report. Make sure that the dates in both tables are in Date/Time format before loading and the device id is a Whole number

After loading go to the Fact table and add new column enter something similar into the measure field

Shift = MAXX(FILTER(Plan, 'Fact'[deviceid] = Plan[DeviceID] && 'Fact'[timestamp] >= Plan[start] && 'Fact'[timestamp] < Plan[end]), Plan[Shiftplan])

This is looking at the device id and then asking if the timestamp is between the startand end dates in the plan table and retruning the Shift where the conditions are met.

I have created a file that you can download and check.

https://drive.google.com/file/d/1ht-FbYCQ3y0wXL25FxIBlF4JjA3zGOvy/view?usp=sharing

Thanks

Joe

If this post helps, then please Accept it as the solution

[Chocohammer]

Hi @JoeBarry 

Thank you one more for your fast reply!

I'm very grateful, I didn't think about that option if I didn't have a relationship between the Plan table and the Fact table. I can use the MAXX FILTER. Tbh. I can't use MAXX or MAX very efficiently, so maybe I was intently not used in it. I know at least I need more training now, about MAX function.

Also I'm sorry, it's true that I'm already filtering the Plan in Power Query that only false is loaded. DeviceID is always a whole number and both tables had the format of timestamp DATE/TIME.

You are a life saver. It works perfectly. Here's the proof.

Now I can continue on my project. 

Thank you so much and have a wonderful day!

Best regards
Choco