topic Re: Calculated column for first occurrence during different times of the day in DAX Commands and Tips

Calculated column for first occurrence during different times of the day

JenWilson — Thu, 20 Jun 2024 16:03:43 GMT

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!

Re: Calculated column for first occurrence during different times of the day

mark_endicott — Thu, 20 Jun 2024 16:58:53 GMT

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.

Re: Calculated column for first occurrence during different times of the day

JenWilson — Thu, 20 Jun 2024 18:14:50 GMT

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?

Re: Calculated column for first occurrence during different times of the day

JenWilson — Thu, 20 Jun 2024 19:36:28 GMT

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

Re: Calculated column for first occurrence during different times of the day

mark_endicott — Fri, 21 Jun 2024 09:00:47 GMT

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.

Re: Calculated column for first occurrence during different times of the day

JenWilson — Fri, 21 Jun 2024 16:08:14 GMT

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.

Re: Calculated column for first occurrence during different times of the day

Anonymous — Mon, 24 Jun 2024 08:05:06 GMT

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.

Re: Calculated column for first occurrence during different times of the day

mark_endicott — Mon, 24 Jun 2024 08:32:56 GMT

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, Anonymous has given an option, but we would need to know what you are trying to acheive to get the full answer.

Re: Calculated column for first occurrence during different times of the day

JenWilson — Tue, 25 Jun 2024 15:01:43 GMT

Anonymous thank you for your response. I ended up coming up with another approach for my second request. thank you!