Which function to calculate a range of time based on different columns

[Julbak]

Hi everyone,

I am currently struggling with an issue that I am not able to solve by myself 😞

Actually I am currently working on a daily analysis for my production results. As you may know, we have Fabrication orders (OF in the database) that run during a day between two date/time values. 

For example:

Of = Fabrication order ID

Début 0F = "Start date/time" for the start declaration of one semi-finished products in the OF considered

Fin OF = "End date/time" for the end declaration

For some days, my code is working perfeclty but for others no as i have got some "Holes" between OF that are not calculated automatically. Example from this table: "The transition from OF 11008173 finished at "12:18:01" and the following OF 11008216 only starts at "13:58:11". So I have got a hole of "1:40:10" between that it is not considered in my calculation. This difference must be added to the next so the OF 11008216.

Below is my function to get the calculation (it works for other days when the transition time is NULL):

For my measures:

OF Min/Max = MIN/MAX of the OF Date

Day Min/Max = MIN/MAX of the "Début OF" and "Fin OF" for all OF (to know the complete data range)

DDD = value of the "Début OF"

DFD = value of the "Fin OF"

HA = "05:00:00" => This is a variable to change my date focus (00:00:00 or 05:00:00 according to the production rythm)

Durée OF (h) =

var summarizedOF = ADDCOLUMNS(

    SUMMARIZE('Durée OF'

    , 'Durée OF'[Date production]

    , 'Durée OF'[OF])

    , "DURATION",

    IF([OF Min] = [DDD] && [OF Min] = [DAY Min]  && [OF Max] = [DAY Max]

        , DATEDIFF([Sélection HA (h)], [OF Max],SECOND)/3600  - HOUR([HA])

        , IF([DFD] <= [OF Max] && [DDD] <> [OF Min]

            , 0

            , DATEDIFF([OF Min], [OF Max],SECOND)/3600

        )

    )

    )

RETURN

SUMX(summarizedOF, IF([DURATION] > 0, [DURATION]))

Below is the result:

As you can see, I find 21,63 hours instead of 24 hours due to this transition. 

I tried several things but I was not able to find something easy, maybe your experience can help me to solve this.

For my example above, the idea will be to consider as "Start date" of OF 11008216" the "End date" of the OF 11008173

Thanks in advance!

[Julbak]

Hello !

Thanks a lot for the tip ! Actually i never used the DAX functions EARLIER and ISBLANK so now i understand the way how i can calculate the duration and how it iterates between rows. The calculation seems to work but I need to adjust a bit according to my database (maybe using DESC)

Thanks !

[123abc · Accepted solution]

To do this in Power BI using DAX, you can consider the following approach:

1. Identify Time Gaps: First, you need to identify the time gaps between the end of one order and the start of the next order. You can achieve this by comparing the end time of one order with the start time of the next order.

2. Adjust Duration Calculation: Then, when calculating the duration of each order, you need to consider these time gaps and adjust the duration accordingly.

   Here's a simplified version of how you can achieve this in Power BI DAX:

   Duration OF (h) =
   VAR OrdersWithGaps =
   ADDCOLUMNS (
   'Durée OF',
   "NextOFStartTime",
   CALCULATE (
   MIN ( 'Durée OF'[Début OF] ),
   FILTER (
   'Durée OF',
   'Durée OF'[Début OF] > EARLIER ( 'Durée OF'[Fin OF] )
   )
   )
   )
   RETURN
   SUMX (
   OrdersWithGaps,
   IF (
   NOT ISBLANK ( [NextOFStartTime] ),
   // If there's a gap, calculate the duration between the end of the current OF and the start of the next OF
   ( [NextOFStartTime] - 'Durée OF'[Fin OF] ) * 24, // Convert to hours
   // If there's no gap, simply calculate the duration of the current OF
   ( 'Durée OF'[Fin OF] - 'Durée OF'[Début OF] ) * 24 // Convert to hours
   )
   )

   It seems like you're trying to calculate the duration of fabrication orders in Power BI, accounting for any time gaps between the end of one order and the start of the next. If I understand correctly, you want to adjust your calculation to include the time gap between the end of one order and the start of the next order.

   To do this in Power BI using DAX, you can consider the following approach:

   1. Identify Time Gaps: First, you need to identify the time gaps between the end of one order and the start of the next order. You can achieve this by comparing the end time of one order with the start time of the next order.

   2. Adjust Duration Calculation: Then, when calculating the duration of each order, you need to consider these time gaps and adjust the duration accordingly.

      Here's a simplified version of how you can achieve this in Power BI DAX:

      DAXCopy code

      Duration OF (h) = VAR OrdersWithGaps = ADDCOLUMNS ( 'Durée OF', "NextOFStartTime", CALCULATE ( MIN ( 'Durée OF'[Début OF] ), FILTER ( 'Durée OF', 'Durée OF'[Début OF] > EARLIER ( 'Durée OF'[Fin OF] ) ) ) ) RETURN SUMX ( OrdersWithGaps, IF ( NOT ISBLANK ( [NextOFStartTime] ), // If there's a gap, calculate the duration between the end of the current OF and the start of the next OF ( [NextOFStartTime] - 'Durée OF'[Fin OF] ) * 24, // Convert to hours // If there's no gap, simply calculate the duration of the current OF ( 'Durée OF'[Fin OF] - 'Durée OF'[Début OF] ) * 24 // Convert to hours ) )

      Explanation:

      • ADDCOLUMNS: This function adds a new column to the table which contains the start time of the next order.
      • CALCULATE: This function calculates the start time of the next order using the MIN function and a filter to find the earliest start time after the current order's end time.
      • SUMX: This function iterates over each row of the table and sums up the durations. If there's a gap, it calculates the duration between the end of the current order and the start of the next order. Otherwise, it calculates the duration of the current order.
      • ISBLANK: This function checks if there's a gap between orders.
      • EARLIER: This function refers to the current row context.

        This code should help you adjust your calculation to include the time gaps between orders in your analysis. Make sure to adapt it to your specific data model and column names as necessary.

        If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

        In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.