Forum Discussion

Peter_2020's avatar
Peter_2020
Helper III
2 years ago
Solved

DATEDIFF with specific conditions

Hi all, I would like to ask you for help with following situation:

 

I have the table with the list of the product numbers and for each product I have START and END date/time. And I want to calculate duration between Start and End date/time but without weekends and I am working only on 2 shifts - it means from 6:00 - 14:00 first shift and 14:00 - 22:00 second shift. So time period 22:00 - 6:00 must be out of the calculation.

 

Here is example:

62 hours is calculated using formula with DATEDIFF, but it calculate time including weekend and 24 hours per day. By using proper formula, expected result is - 6 hours.

 

10.11.2023 - 18:00 - 22:00 - 4hours

11.11.2023 - weekend - 0 hours

12.11.2023 - weekend - 0 hours

13.11.2023 - 6:00 - 8:00 - 2 hours

 

Any idea how to do this?

Thank you in advance!

P.

Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    Peter_2020 

    I just got some time so I re wrote the formula with break time and based on minute calcualtion. please check the attached file:

     

    Production Time = 
VAR __SHIFTSTART = TIME( 6,0,0 )      -- Provide shift start hour 
VAR __SHIFTEND = TIME( 22,0,0 )       -- Provide shift end hour
VAR __START = Production[Start]
VAR __END =  Production[End]
VAR __PERIOD =  
	FILTER( 
		GENERATESERIES( __START, __END,  1/24/60 ) , 
		VAR __TimeVal = MOD( [Value] , 1 ) RETURN
		NOT WEEKDAY( [Value] , 1) IN {1,7} 		
		&&   __TimeVal >=  __SHIFTSTART  && __TimeVal <=  __SHIFTEND
		&&
           ISEMPTY( 
                FILTER(
                    Breaks,
                    AND( 
                        __TimeVal >= Breaks[Break Start] , __TimeVal <= Breaks[Break End]
                    )
                )
            )		
	) 
VAR __TOTALMIN = COUNTROWS( __PERIOD )
VAR __RESULT = 
    FORMAT( INT( DIVIDE(__TOTALMIN , 60) ) , "00:" ) & FORMAT(  MOD(__TOTALMIN , 60 ) , "00" )
RETURN 
    __RESULT

     

     

     



5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Peter_2020 

    Add a calculated column using the DAX code below:

    Produnction Time = 
VAR __SHIFTSTART = 6      -- Provide shift start hour 
VAR __SHIFTEND = 22       -- Provide shift end hour
VAR __START = Table1[Start]
VAR __END =  Table1[End] -  TIME(1,0,0)
VAR __SHIFTPERIOD = GENERATESERIES( __SHIFTSTART , __SHIFTEND - 1  , 1 )
VAR __PERIOD =  FILTER( GENERATESERIES( __START, __END,  TIME(1,0,0) ) , NOT WEEKDAY( [Value] , 1 ) IN {1,7} )

RETURN
    COUNTROWS(
        FILTER(
            __PERIOD,
            HOUR( [Value] ) IN  __SHIFTPERIOD
        )
    )

     



    • Peter_2020's avatar
      Peter_2020
      Helper III
      2 years ago

      Fowmy Thank you so much! This is exactly what I needed.

       

      But I have one more challenge - is it possible to define the breaks during the shift? Example: BREAK1 - 9:00 - 9:30, BREAK2 - 16:00 - 16:10 and that time will be of course out of the calculated production time...

      I tried to add this into the formula but I don´t know how to define minutes and how will looks second part of the formula:

       

      PRODUCTION TIME2 =
      VAR __SHIFTSTART = 6
      VAR __SHIFTEND = 22  
      VAR __SHIFTBREAKSTART1 = 9
      VAR __SHIFTBREAKEND1 = 10  
      VAR __SHIFTBREAKSTART2 = 16
      VAR __SHIFTBREAKEND2 = 17  
      VAR __START = Table[Start date/time]
      VAR __END =  Table[End date/time] -  TIME(1,0,0)
      VAR __SHIFTPERIOD = GENERATESERIES( __SHIFTSTART , __SHIFTEND - 1  , 1 )
      VAR __SHIFTBREAKPERIOD1 = GENERATESERIES( __SHIFTBREAKSTART1 , __SHIFTBREAKEND1 - 1  , 1 )
      VAR __SHIFTBREAKPERIOD2 = GENERATESERIES( __SHIFTBREAKSTART2 , __SHIFTBREAKEND2 - 1  , 1 )
      VAR __PERIOD =  FILTER( GENERATESERIES( __START, __END,  TIME(1,0,0) ) , NOT WEEKDAY( [Value] , 1 ) IN {1,7} )

      RETURN
          COUNTROWS(
              FILTER(
                  __PERIOD,
                  HOUR( [Value] ) IN  {__SHIFTPERIOD,__SHIFTBREAKPERIOD1,__SHIFTBREAKPERIOD2}
              )
          )
       
      Thank you for your help!
      P.
      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Peter_2020 

        I just got some time so I re wrote the formula with break time and based on minute calcualtion. please check the attached file:

         

        Production Time = 
VAR __SHIFTSTART = TIME( 6,0,0 )      -- Provide shift start hour 
VAR __SHIFTEND = TIME( 22,0,0 )       -- Provide shift end hour
VAR __START = Production[Start]
VAR __END =  Production[End]
VAR __PERIOD =  
	FILTER( 
		GENERATESERIES( __START, __END,  1/24/60 ) , 
		VAR __TimeVal = MOD( [Value] , 1 ) RETURN
		NOT WEEKDAY( [Value] , 1) IN {1,7} 		
		&&   __TimeVal >=  __SHIFTSTART  && __TimeVal <=  __SHIFTEND
		&&
           ISEMPTY( 
                FILTER(
                    Breaks,
                    AND( 
                        __TimeVal >= Breaks[Break Start] , __TimeVal <= Breaks[Break End]
                    )
                )
            )		
	) 
VAR __TOTALMIN = COUNTROWS( __PERIOD )
VAR __RESULT = 
    FORMAT( INT( DIVIDE(__TOTALMIN , 60) ) , "00:" ) & FORMAT(  MOD(__TOTALMIN , 60 ) , "00" )
RETURN 
    __RESULT