- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculated column for first occurrence during different times of the day
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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 as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@v-zhouwen-msft thank you for your response. I ended up coming up with another approach for my second request. thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
01-12-2024 08:23 AM | |||
04-13-2024 01:19 PM | |||
03-18-2024 11:08 AM | |||
01-07-2024 03:33 AM | |||
01-25-2024 06:49 AM |
User | Count |
---|---|
13 | |
12 | |
11 | |
7 | |
7 |
User | Count |
---|---|
16 | |
13 | |
12 | |
9 | |
9 |