DAX Combine Date Column and Time Column into DateTime Column

DAX DAX Calculations
1

Hi - I need help in getting my Date Col and Time Col together into a new DateTime column, I have tried loads of things including

Date Time = CN_FXD_PRICE_CHNG_LOG[CFPL_DATE] & " " & TIME(HOUR(CN_FXD_PRICE_CHNG_LOG[Time]), MINUTE(CN_FXD_PRICE_CHNG_LOG[Time]), SECOND(CN_FXD_PRICE_CHNG_LOG[Time]))

and

Date Time = DATE(YEAR(CN_FXD_PRICE_CHNG_LOG[CFPL_DATE]), MONTH(CN_FXD_PRICE_CHNG_LOG[CFPL_DATE]), DAY(CN_FXD_PRICE_CHNG_LOG[CFPL_DATE])) & TIME(HOUR(CN_FXD_PRICE_CHNG_LOG[Time]), MINUTE(CN_FXD_PRICE_CHNG_LOG[Time]), SECOND(CN_FXD_PRICE_CHNG_LOG[Time]))

but can’t get anything to work, pbix file attached thank you for any pointers you can give. Lizzie

1 Like
2

Hi Lizzie,

that PBIX table, where your columns reside are from your Direct Query model so I can’t view them, seeing the data you’re dealing with would help.

Are you able to post a dummy PBIX? One thing I would add, it’s always a good idea to mask data when publishing onto the forum so you don’t publish anything sensitive.

David

2 Likes
3

Hello @LizzieTompsett Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Hope it helps. x

4

Hi @LizzieTompsett,

I create a really simple example with a Date columns and a Time columns.
I use Power Query to concatenate the two columns in the right format using a Custom function.

You can see my example below.
Book1.xlsx (10.8 KB)
Untitled.pbix (55.8 KB)

I hope it will help you.

Best regards,
Joaly

2 Likes
5

Hi Joaly
Thanks for your post, I created a dummy pbix file too and the DAX measures all worked so it must be something to do with the date field coming from the ERP system. As it is a direct query I can’t use the concatenate in Power Query. I spoke to the IT company and they said the field has a DateTime data type and cannot be changed.
What seems to be happening is that for e.g. 31/07/2021 is actually 31/07/2021 00:00:00 and then won’t properly join with another time field.
I am trying to find a way in Power BI to get rid of the 00:00:00…
Thanks Lizzie

6

Your problem is clearer now.
I’ll come back to you if I find a solution :slight_smile:

7

Is a formula like this one works in your model ?
image

Using a “+” instead of a “&”.

1 Like
8

Oh my goodness Joaly - you are a genius!!!
The + instead of the & worked, thank you so much for your help, I have spent days trying different things!!!

Best regards Lizzie

2 Likes
9

Your welcome @LizzieTompsett
It’s a pleasure :slight_smile:

Have a nice day,
Joaly

2 Likes