Forum Discussion
New column in DAX
I have two columns:
ID Creation Date
1 26.07.2023 09:30:05
2 26.07.2023 12:35:32
3 26.07.2023 15:44:22
4 26.07.2023 17:34:05
5 27.07.2023 08:54:09
6 27.07.2023 09:35:07
7 27.07.2023 12:30:11
8 27.07.2023 14:34:44
9 27.07.2023 16:54:22
10 27.07.2023 18:59:07
Let's assume that there is a queue of three IDs per day and that we only consider the hours of 9-17. I need an extra column that will assign a new date based on the queue.
ID Creation Date New Date
1 26.07.2023 09:30:05 26.07.2023 09:30:05 (first ID for date 26.07)
2 26.07.2023 12:35:32 26.07.2023 12:35:32 (second ID for date 26.07)
3 26.07.2023 15:44:22 26.07.2023 15:44:22 (third ID for date 26.07)
4 26.07.2023 16:34:05 27.07.2023 09:00:00 (fourth ID for date 26.07, so have to move to the next day at 09:00:00)- first ID for date 27.07
5 27.07.2023 08:54:09 27.07.2023 09:00:00 (Creation Date before 09:00:00, so have to move at 09:00:00) - second ID for date 27.07
6 27.07.2023 09:35:07 27.07.2023 09:35:07 (third ID for date 27.07)
7 27.07.2023 12:30:11 28.07.2023 09:00:00 (the queue of three IDs for this day has passed, so we move to the next day) - first ID for date 28.07)
8 27.07.2023 14:34:44 28.07.2023 09:00:00 (second ID for date 28.07)
9 27.07.2023 16:54:22 28.07.2023 09:00:00 (third ID for date 28.07)
10 27.07.2023 18:59:07 29.07.2023 09:00:00 (first ID for date 29.07 because queue for three ID'S has passed both 27.07 and 28.07)
Hi Mateusz00 ,
You can create the calculated columns as below to get it:
NextDtime = VAR _index = ROUNDUP ( 'Table'[ID] / 3, 0 ) - 1 RETURN ( DATEVALUE ( MIN ( 'Table'[Creation Date] ) ) + _index ) & " " & TIME ( 9, 0, 0 )New Date = IF ( 'Table'[Creation Date] >= 'Table'[NextDtime], 'Table'[Creation Date], 'Table'[NextDtime] )Best Regards
7 Replies
Hi Mateusz00 ,
You can create the calculated columns as below to get it:
NextDtime = VAR _index = ROUNDUP ( 'Table'[ID] / 3, 0 ) - 1 RETURN ( DATEVALUE ( MIN ( 'Table'[Creation Date] ) ) + _index ) & " " & TIME ( 9, 0, 0 )New Date = IF ( 'Table'[Creation Date] >= 'Table'[NextDtime], 'Table'[Creation Date], 'Table'[NextDtime] )Best Regards
eliasayyyMemorable Member
hello i tried your response and it works , iwas wondering if you could help me add 1 hour between each meeting , lets say i can only take 1 meeting at a time, how can i add 1 hour difference
expected result
Meeting Schedule7/26/2023 9:30:05 AM 7/26/2023 12:35:32 PM 7/26/2023 3:44:22 PM 7/27/2023 9:00:00 AM 7/27/2023 10:00:00 AM 7/27/2023 11:00:00 AM 7/28/2023 9:00:00 AM 7/28/2023 10:00:00 AM 7/28/2023 11:00:00 AM 7/29/2023 9:00:00 AM
Hello, thank you for response but that is not exactly what I mean. If you check your "New Date" and "New Date" which I wrote there are differences.
Hi Mateusz00 ,
What's your expected result? For the field [New Date], is there anything else necessary be included in besides the datetime? Could you please explain more details with the specific examples? Thank you.
Best Regards
Of course, the point is that at 9 o'clock we transfer the ID to the following days in two cases:
1) when the queue for the day ends
2) when the Creation Date is outside of 9am-5pm.
If the ID is in the queue for a given day and the Creation Date is between 9-17, the date stays the same (ID No. 1 from my example)