Forum Discussion

Chocohammer's avatar
Chocohammer
Frequent Visitor
3 years ago
Solved

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

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
Need Help
  • JoeBarry's avatar
    JoeBarry
    3 years ago

    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

     

     

5 Replies

  • JoeBarry's avatar
    JoeBarry
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      Chocohammer
      Frequent Visitor
      3 years ago

      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's avatar
        JoeBarry
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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's avatar
    Chocohammer
    Frequent Visitor
    3 years ago

    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