Forum Discussion

Peter_2020's avatar
Peter_2020
Icon for Helper III rankHelper 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 calcula...
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

     

     

     



Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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
      Icon for Super User rankSuper 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

       

       

       



      • Peter_2020's avatar
        Peter_2020
        Icon for Helper III rankHelper III
        2 years ago

         Fowmy it works perfectly. Thank you so much for your help!