Calculated column for first occurrence during different times of the day

[JenWilson]

I am looking to create a new column (manual example called Diff added to screen shot below for what I am looking for. It can be in query or directly in the table. I would like the new column to calculate the time difference between the time in the ProdScheduleStartTimesbased and the BeginTime column but also based on the BeginDate. Perhaps using the combined column StartTime would work better? It only needs to calculate the time difference for the first occurance (for each date) of each item listed in the each ProductionSchedule (Period1, Period 2, etc.) I have manually added what I am looking for to the far right (the Diff column). Basically, I want to see how long it took for the first production to start against when the shift (Period) began. In the first line, you can see that Period 1 starts at 5:00:00 AM, but the first production job didn't start running until 5:26:37 AM so I want to see the difference value of 00:26:37. Farther down, as another example, you can see that Period 2 starts at 7:05:00 AM, but the first production job for that periond didn't start running until 7:09:06 AM, so I want to see the difference value of 00:04:06. 

Can someone please help me come up with a solution to this? Thank you! 

[JenWilson]

This is where I am at with the following DAX code. I need to to calculate for each date and each period. What do I need to add to my DAX formula to make this work? 

Difference =

VAR period = Eagle1_and_2[ProductionSchedule]

VAR min_start_time =

    CALCULATE (

        MIN(Eagle1_and_2[BeginTime] ),

        ALLEXCEPT ( Eagle1_and_2, Eagle1_and_2[ProductionSchedule] )

    )

VAR diff = Eagle1_and_2[ProdStartTimes] - Eagle1_and_2[BeginTime]

VAR calc =

    IF (

        Eagle1_and_2[ProductionSchedule]= period

            && Eagle1_and_2[BeginTime] = min_start_time,

        CALCULATE ( diff )

    )

RETURN

    calc

[mark_endicott · Accepted solution]

@JenWilson - Sorry for the use of MAX in my code, I didnt read your requirements properly and one of the DIFF values in your screenshot was not on the MAX or MIN (05:26:37 AM). But that's still my mistake. 

Your screenshot only had one day too, so it wasnt clear this would need to cover multiple dates, my code below has this updated - you just need to add your "Date" column into the ALLEXCEPT(). 

Diff = 
VAR period = Query1[ProdSchedule]
VAR min_start_time =
    CALCULATE (
        min ( Query1[BeginTime] ),
        ALLEXCEPT ( Query1, Query1[ProdSchedule], Query1[Date] )
    )
VAR diff = Query1[ProdScheduleStartTime] - Query1[BeginTime]
VAR calc =
    IF (
        Query1[ProdSchedule] = period
            && Query1[BeginTime] = min_start_time,
        CALCULATE ( diff )
    )
RETURN
    calc

If this works, please accept as the solution. 

[JenWilson]

@mark_endicott , Yes, this worked pefectly! Thank you so much! Now, is there a way to subtract this value from another measure that I have in my data set? I'm guessing not. 

[mark_endicott]

@JenWilson - Great to hear it worked. Could you mark my message with the MIN change as the solution? It answered your original query.

With regards to subtracting it from a measure in your model, @v-zhouwen-msft has given an option, but we would need to know what you are trying to acheive to get the full answer. 

[v-zhouwen-msft]

Hi @mark_endicott ,Thanks for your quick reply, I'll add more.

Hi @JenWilson ,

Regarding your question, do you want to create another measure or calculated column to calculate the difference? If it is a measure, since you cannot directly reference the column name in the measure, you need to use an aggregate function to reference the column.

Something like this.

Difference = [Measure] - MAX('Table'[Diff])

If I understood wrongly, please provide more information.

[JenWilson]

@v-zhouwen-msft thank you for your response. I ended up coming up with another approach for my second request. thank you! 

[mark_endicott]

@JenWilson - The DAX below will work in a calculated column (you just need to change the table names):

Diff = 
VAR period = Query1[ProdSchedule]
VAR max_start_time =
    CALCULATE (
        MAX ( Query1[BeginTime] ),
        ALLEXCEPT ( Query1, Query1[ProdSchedule] )
    )
VAR diff = Query1[ProdScheduleStartTime] - Query1[BeginTime]
VAR calc =
    IF (
        Query1[ProdSchedule] = period
            && Query1[BeginTime] = max_start_time,
        CALCULATE ( diff )
    )
RETURN
    calc

Screenshot to show it is working:

If this works, please mark it as the solution. 

[JenWilson]

@mark_endicott Hi, thanks for your quick response.  I actually need the time calculations to be based off of the earlier time occurance so I changed MAX to MIN. However, it is only doing the calculation on one date - the date and period with the smallest varience I assume. So, I'm guessing that I need to added some addtional details around the date as well?