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,

     

MaaPer's avatar
MaaPer
Frequent Visitor
6 years ago

Dear v-lid-msft 

I was reviewing the first table and I see something that can't understand how to solve.

The issue is that segment 5 in fact should strat at 14:39 and finish at 14:56, and from 14:39 to 14:56, boths lines have produced 9 items. Any idea on how to correct that?

Another topic is about the segments. For example segment 1 start at 9:31 with the firt production unit and ended at 9:54 with the end of the last production unit, so the time for that segment should be the difference (23 minutes instead of 17) thats why the time between units should be considered as "work time" unless it is 10 minutes or higher.

Many thanks again for your help.

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
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,

 

  • MaaPer's avatar
    MaaPer
    Frequent Visitor
    6 years ago

    Dear v-lid-msft ,

    Your solution works like magic!

    I'm sure it will take some days to me to fully understand it, but I'm very gratefull for giving me that help and that opportunity to continue learning.

    Do you mind if I'll get back to you with some doubts about the solution in the following days?. I want to take the opportunity to learn about it and not only make a copy-paste.

    Many thanks again.

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi MaaPer ,

       

      Sure, If you have any other questions, please kindly ask here and we will try to resolve it. But beg to be excused that we may have some delay in response.


      Best regards,