New column in DAX

[Mateusz00]

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)

[Mateusz00]

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.

[Anonymous]

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

[Mateusz00]

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)

[Anonymous]

Hi @Mateusz00 ,

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

Best Regards

[Mateusz00]

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

[Anonymous · Accepted solution]

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

[eliasayyy]

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