topic Re: New column in DAX in DAX Commands and Tips

New column in DAX

Mateusz00 — Wed, 26 Jul 2023 04:48:51 GMT

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)

Re: New column in DAX

Anonymous — Fri, 28 Jul 2023 06:07:21 GMT

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

Re: New column in DAX

Mateusz00 — Fri, 28 Jul 2023 06:20:16 GMT

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.

Re: New column in DAX

Anonymous — Fri, 28 Jul 2023 06:31:58 GMT

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

Re: New column in DAX

Mateusz00 — Fri, 28 Jul 2023 06:40:44 GMT

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)

Re: New column in DAX

Anonymous — Fri, 28 Jul 2023 06:48:56 GMT

Hi Mateusz00 ,

As compared your provided info with mine, they are same... Am I misunderstanding something?

Best Regards

Re: New column in DAX

eliasayyy — Fri, 28 Jul 2023 07:08:46 GMT

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 Schedule

7/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

Re: New column in DAX

Mateusz00 — Fri, 28 Jul 2023 07:18:33 GMT

Sorry, I was looking at the wrong column (NextDtime). Thank you