Solving for hours with accumulated time stamps and filtering for multiple variables

[JP1972]

Hi, 

It's too much for my old brain and I need help please.  I'm attempting to solve a data chasm within this model.  I've tried breaking it down and helper columns, but I can't seem to make it make sense.  I have 3 months of data resulting in 15K rows.  My end goal is to find the average foot per hour for that shift on that specific day based on the following heirarchal variables: 

• WOC Datestamp
• Shift
• Work Center
• Item

My first requirement is to sum the 'Footage Produced' based on the variables above. 1440, 6240, 2160, etc.....

The second requirement is to find the total decimal hours between the first timestamp (06:28) and the last timestamp (16:09) minus 1 hour for lunch and 2 breaks.

The third requirement is to divide the calculated hours by the calculated hours within the filtered variables above to arrive at a average foot per hour.

The result on paper is 56,640ft / 8.683hrs = 6,523.09ft/hr average.

Your help would be greatly appreciated.

[Anonymous · Accepted solution]

Hi @JP1972 ,

1. Create a measure to get the sum of Footage Produced.

Sum Footage Produced =
CALCULATE(SUM('Table'[Footage Producted]),FILTER(ALL('Table'),'Table'[WOC Datetamp] = MAX('Table'[WOC Datetamp]) && 'Table'[Shift] = MAX(' Table'[Shift]) && 'Table'[Work Center] = MAX('Table'[Work Center]) && 'Table'[Item] = MAX('Table'[Item]))))

2. Create a MEASURE to get the difference between the maximum and minimum time.

Diff =
VAR _max_time = CALCULATE(MAX('Table'[WOC Timestamp]),FILTER(ALL('Table'),'Table'[WOC Datetamp] = MAX('Table'[WOC Datetamp]) && 'Table'[ Shift] = MAX('Table'[Shift]) && 'Table'[Work Center] = MAX('Table'[Work Center]) && 'Table'[Item] = MAX('Table'[Item]))))
VAR _min_time = CALCULATE(MIN('Table'[WOC Timestamp]),FILTER(ALL('Table'),'Table'[WOC Datetamp] = MAX('Table'[WOC Datetamp]) && 'Table'[ Shift] = MAX('Table'[Shift]) && 'Table'[Work Center] = MAX('Table'[Work Center]) && 'Table'[Item] = MAX('Table'[Item]))))
var _a=DATEDIFF(_min_time,_max_time,MINUTE)
RETURN
(_a/60) - 1

3. Create MEASURE to get the average value.

ave = 'Table'[Sum Footage Produced] / 'Table'[Diff]

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @JP1972 ,

1. Create a measure to get the sum of Footage Produced.

Sum Footage Produced =
CALCULATE(SUM('Table'[Footage Producted]),FILTER(ALL('Table'),'Table'[WOC Datetamp] = MAX('Table'[WOC Datetamp]) && 'Table'[Shift] = MAX(' Table'[Shift]) && 'Table'[Work Center] = MAX('Table'[Work Center]) && 'Table'[Item] = MAX('Table'[Item]))))

2. Create a MEASURE to get the difference between the maximum and minimum time.

Diff =
VAR _max_time = CALCULATE(MAX('Table'[WOC Timestamp]),FILTER(ALL('Table'),'Table'[WOC Datetamp] = MAX('Table'[WOC Datetamp]) && 'Table'[ Shift] = MAX('Table'[Shift]) && 'Table'[Work Center] = MAX('Table'[Work Center]) && 'Table'[Item] = MAX('Table'[Item]))))
VAR _min_time = CALCULATE(MIN('Table'[WOC Timestamp]),FILTER(ALL('Table'),'Table'[WOC Datetamp] = MAX('Table'[WOC Datetamp]) && 'Table'[ Shift] = MAX('Table'[Shift]) && 'Table'[Work Center] = MAX('Table'[Work Center]) && 'Table'[Item] = MAX('Table'[Item]))))
var _a=DATEDIFF(_min_time,_max_time,MINUTE)
RETURN
(_a/60) - 1

3. Create MEASURE to get the average value.

ave = 'Table'[Sum Footage Produced] / 'Table'[Diff]

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JP1972 · Accepted solution]

I was able to get the measure done with this mod

Sum Footage Produced =

CALCULATE(SUM('DEC23 to FEB29 Prod Data'[Footage Produced]),

    FILTER(ALL('DEC23 to FEB29 Prod Data'),'DEC23 to FEB29 Prod Data'[WOC Datestamp] = EARLIER('DEC23 to FEB29 Prod Data'[WOC Datestamp])))

It aggregates the sum for the calendar date for all work centers.

What's giving me a fit now is the Min & Max measure.  I've pulled the filters and I get the Min/Max for the entire column.  I'm trying to specifically filter the instances where Workcenter, Item, WOC Datestamp and Shift are the same in Row Context.  Here's where I'm at;

Shift Hours =

VAR _max_time =

    CALCULATE(

        MAX('DEC23 to FEB29 Prod Data'[WOC Timestamp]),

        FILTER(

            ALL('DEC23 to FEB29 Prod Data'),'DEC23 to FEB29 Prod Data'[WOC Datestamp] = MAX('DEC23 to FEB29 Prod Data'[WOC Datestamp])))

VAR

    _min_time = CALCULATE(

        MIN('DEC23 to FEB29 Prod Data'[WOC Timestamp]),FILTER(

            ALL('DEC23 to FEB29 Prod Data'),'DEC23 to FEB29 Prod Data'[WOC Datestamp] = MIN('DEC23 to FEB29 Prod Data'[WOC Datestamp])))

var _a=DATEDIFF(_min_time,_max_time,MINUTE)

RETURN

(_a/60) - 1

Thank you for your help with this matter.