Forum Discussion

MaaPer's avatar
MaaPer
Frequent Visitor
6 years ago
Solved

How to calculate productivity

Dear all, For days I've been trying to find out how to approach a problem and I can't find out where to start looking for the solution and I'm going to this forum to ask if anyone can guide me on ho...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi MaaPer ,

     

    First of all, please create a index column in power query editor, then create calculated colmun using following: 

     

     

    Calculated column:

    Time between Units = 
DATEDIFF (
    [Process started],
    CALCULATE (
        MIN ( 'Table'[Process started] ),
        'Table','Table'[Index] > EARLIER('Table'[Index])
        
    ),
    MINUTE
)
    Work Segment = 
"Segment "
    & ROUNDDOWN(CALCULATE (
        DISTINCTCOUNT ( 'Table'[Time between Units] ),
        'Table',
        'Table'[Process finished] < EARLIER ( 'Table'[Process finished] ),
        'Table'[Time between Units] > 10
    ),0) + 1

     

    Then create a new measure:

    Work Time in Segment = DATEDIFF(MIN('Table'[Process started]),MAX('Table'[Process finished]),MINUTE)

     

    Update old measure as following:

    Units/hr in Segment = DISTINCTCOUNT('Table'[Items produced]) / [Work Time in Segment] * 60
    Items = 
VAR fromTime =
    MIN ( 'TimeDim'[From] )
VAR toTime =
    MAX ( 'TimeDim'[To] )
    VAR nowTime =
    DATE ( 2020, 3, 24 ) + TIME ( 14, 56, 0 )
RETURN
IF( MINUTE ( toTime ) = MINUTE ( nowTime )
            && DATEDIFF ( toTime, nowTime, MINUTE ) <= 60 * 8
            && DATEDIFF ( toTime, nowTime, MINUTE ) >= 0,CALCULATE(DISTINCTCOUNT('Table'[Items produced]),'Table'[Process finished] > fromTime,'Table'[Process finished] <= toTime))
    Effective Work Time = 
VAR fromTime =
    MIN ( 'TimeDim'[From] )
VAR toTime =
    MAX ( 'TimeDim'[To] )
VAR nowTime =
    DATE ( 2020, 3, 24 ) + TIME ( 14, 56, 0 )
RETURN
    IF (
        MINUTE ( toTime ) = MINUTE ( nowTime )
            && DATEDIFF ( toTime, nowTime, MINUTE ) <= 60 * 8
            && DATEDIFF ( toTime, nowTime, MINUTE ) >= 0,
        CALCULATE (
            SUM ( 'Table'[Time of processing] ),
            'Table'[Process started] >= fromTime,
            'Table'[Process finished] <= toTime
        )
    )

     

     

    If you have any other questions , please kindly ask here and we will try to resolve it.

     

    By the way, PBIX file as attached. 


    Best regards,

     

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hi MaaPer,

 

I had a look at your excel-file, and I think you should be able to do this with DAX.

 

How do you identify breaks? When the difference between end and start of next item on the same line is greater than 10 minutes? 

 

What is you definition of productivity? Items per hour? 

 

Cheers,
Sturla

  • MaaPer's avatar
    MaaPer
    Frequent Visitor
    6 years ago
    Hello Sturla, thanks for your quick reply.
    The breaks are certainly identified by stoppages or more than 10 minutes.
    The point here is that once I identified how to proceed will be easy to change to 15 or 20 in the future.

    Regarding productivity is items per hour (excluding) the breaks. I do ir in excel with the formula. (ITEMS/INTERVAL_TIME)*(1/24)
    1/24 is used to obtain the equivalent to une hour time.

    Thanks again in advance.
    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi MaaPer ,

       

      Based on my research, we found some difference with logic and expected result in your example, such as for line 2 in Item 80, it should have 10 minutes instead of 11 miutes, so it will keep Segment 4. The total worktime of Segment 1 should be 2*7+3=17 minutes instead of 23 minutes.

       

      We make following solution based on the logic you provided:

       

      1. create some calculated column in fact table:

       

      Time of processing = DATEDIFF([Process started],[Process finished],MINUTE)

       

      Time between Units =
DATEDIFF (
    [Process finished],
    CALCULATE (
        MIN ( 'Table'[Process started] ),
        'Table',
        'Table'[Line of production] = EARLIER ( 'Table'[Line of production] ),
        'Table'[Process started] > EARLIER ( 'Table'[Process started] )
    ),
    MINUTE
)

       

      Work Segment =
"Segment "
    & CALCULATE (
        DISTINCTCOUNT ( 'Table'[Time between Units] ),
        'Table',
        'Table'[Line of production] = EARLIER ( 'Table'[Line of production] ),
        'Table'[Process finished] < EARLIER ( 'Table'[Process finished] ),
        'Table'[Time between Units] > 10
    ) + 1

       

       

      2. create a calculated table 

       

      TimeDim = 
VAR nowTime =
    DATE ( 2020, 3, 24 ) + TIME ( 14, 56, 0 )
RETURN
    ADDCOLUMNS (
        CROSSJOIN (
            CALENDAR ( nowTime - 1, nowTime + 2 ),
            SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "Hour", [Value] ),
            SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "Minute", [Value] )
        ),
        "From", [Date] + TIME ( [Hour], [Minute], 0 ),
        "To", [Date]
            + TIME ( [Hour] + 1, [Minute], 0 )
    )

       

      You can replace 

      VAR nowTime =
    DATE ( 2020, 3, 24 ) + TIME ( 14, 56, 0 )

      With 

      VAR toTime =
NOW ()

       

       

       

      3. create following measure used in table visual:

      Items = 
VAR fromTime =
    MIN ( 'TimeDim'[From] )
VAR toTime =
    MAX ( 'TimeDim'[To] )
    VAR nowTime =
    DATE ( 2020, 3, 24 ) + TIME ( 14, 56, 0 )
RETURN
IF( MINUTE ( toTime ) = MINUTE ( nowTime )
            && DATEDIFF ( toTime, nowTime, MINUTE ) <= 60 * 8
            && DATEDIFF ( toTime, nowTime, MINUTE ) >= 0,CALCULATE(DISTINCTCOUNT('Table'[Items produced]),'Table'[Process started] >= fromTime,'Table'[Process finished] <= toTime))

       

      Effective Work Time = 
VAR fromTime =
    MIN ( 'TimeDim'[From] )
VAR toTime =
    MAX ( 'TimeDim'[To] )
VAR nowTime =
    DATE ( 2020, 3, 24 ) + TIME ( 14, 56, 0 )
RETURN
    IF (
        MINUTE ( toTime ) = MINUTE ( nowTime )
            && DATEDIFF ( toTime, nowTime, MINUTE ) <= 60 * 8
            && DATEDIFF ( toTime, nowTime, MINUTE ) >= 0,
        CALCULATE (
            SUM ( 'Table'[Time of processing] ),
            'Table'[Process started] >= fromTime,
            'Table'[Process finished] <= toTime
        )
    )

       

      Units/hr = [Items] / [Effective Work Time] * 60

       

      Units/hr in Segment = DISTINCTCOUNT('Table'[Items produced]) / SUM('Table'[Time of processing]) * 60​

       

      You can replace 

       

      VAR nowTime =
    DATE ( 2020, 3, 24 ) + TIME ( 14, 56, 0 )

       

      With 

       

      VAR toTime =
NOW ()

       

       

       


      If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？


      Best regards,

       

      • MaaPer's avatar
        MaaPer
        Frequent Visitor
        6 years ago

        Dear Dong Li,

        I wanted to thank you for such valuated support.

        Now I understand that I will never get to a valid solution by my own 😁, still have a lot to learn!

        I will takes to me some time to understand the expressions and to confirm that is what i was looking for.

        Meanwhile I wanted to thank you again for your time, i will keep you informed.

         

        Regards,