Change in Efficiency of Manufacturing parts

[JoeKat07]

Hello,

I am trying to find the change in median efficiency of the manufacturing of different parts over time. I'm most interested in finding the change in efficiency of the last time a part was run vs the previous time for all parts. I would also like to look at the efficiency of the production of part ID by month. Here is a look at my data.

Thanks in advance!

[v-jiascu-msft]

Hi @JoeKat07,

1. Add a Calculated Column "DailyEfficiency", which would make this question simple.

DailyEfficiency =
DIVIDE ( [Good Qty], [Hours Worked], 0 )

2. Create a measure "DailyChage". 

DailyChange =
VAR previousDate =
    CALCULATE (
        MAX ( 'Table1'[Transaction Date] ),
        FILTER (
            ALL ( 'Table1'[Transaction Date] ),
            'Table1'[Transaction Date] < MIN ( 'Table1'[Transaction Date] )
        )
    )
VAR previousEfficiency =
    CALCULATE (
        MAX ( 'Table1'[DailyEfficiency] ),
        'Table1'[Transaction Date] = previousDate
    )
RETURN
    IF (
        ISBLANK ( previousDate ),
        0,
        previousEfficiency - MAX ( 'Table1'[DailyEfficiency] )
    )

3. Create another measure "MonthlyEfficiency".

MonthlyEfficiency =
DIVIDE ( SUM ( Table1[Good Qty] ), SUM ( Table1[Hours Worked] ), 0 )

4. Create visuals. Pay attention to the fields.

 

You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgSsSgxbuxWU-vlkS.

Best Regards!

Dale

[JoeKat07]

My database was too large to run the opeartion for the change in efficiency, i kept getting "running out of memory" errors,

The only to find the monthy efficiency works great!

[v-jiascu-msft · Accepted solution]

Hi @JoeKat07,

Since you have a big table, we can do it step by step. The steps below are all created in the Data view.

1. Get the previous date.

PreviousDate =
VAR currentid = [Part ID]
VAR currentDate = [Transaction Date]
RETURN
    CALCULATE (
        MAX ( [Transaction Date] ),
        FILTER (
            'Table1',
            'Table1'[Part ID] = currentID
                && 'Table1'[Transaction Date] < currentDate
        )
    )

2. Get previous efficiency.

PreviousEfficiency =
VAR CurrentDate = [PreviousDate]
VAR CurrentID = [Part ID]
RETURN
    CALCULATE (
        MAX ( [DailyEfficiency] ),
        FILTER (
            'Table1',
            Table1[Transaction Date] = CurrentDate
                && 'Table1'[Part ID] = CurrentID
        )
    )

3. Evaluate the change.

EfficiencyChange =
IF ( [PreviousEfficiency] = 0, 0, [DailyEfficiency] - [PreviousEfficiency] )

Best Regards!

Dale