How to remove milliseconds or round to nearest second

[Anonymous]

So I have a column of date times in a SQL Server databas as per below:

2019-06-20 06:02:55.667
2019-06-20 06:02:55.433
2019-06-20 06:20:00.083
2019-06-20 06:20:00.053
2019-06-20 06:20:00.130

When I create a model in SSAS I get the following rows:

6/20/2019 6:02:55 AM

6/20/2019 6:02:55 AM

6/20/2019 6:20:00 AM

6/20/2019 6:20:00 AM

6/20/2019 6:20:00 AM

At first glance the miliseconds no longer appear. However, when I try to do aggregations based on the date I actually get incorrect results because the miliseconds make these rows appear all different.

What I actually want is to find a way to remove the milliseconds from the datetime. I have tried using the DAX FORMAT function however it actually rounds up the miliseconds and I get the followign results:

20/06/19 06:02:56

20/06/19 06:02:55

20/06/19 06:20:00

20/06/19 06:20:00

20/06/19 06:20:00

As you can see the first two rows now have 56 and 55 seconds. This is because the milliseconds get rounded up.

How can I create a new column which actually has 55 seconds for both first and second row? Is there a way to tell the DAX function to round down the milliseconds? Or just remove them completely?

Thank you

[Anonymous]

Hi! In my case I want to use the rounded up values, can you let me know how did you achieve that?

[HotChilli]

Is this Power BI ? If so, in Power Query you can Split column on the last occurrence of '.' (This must be before the data type is changed to DateTime (or similar).

If not, I think you can use the same Power Query functionality in newer versions of Analysis Services.

[Anonymous]

Yes, it is indeed Power BI. However, it is a live connection from SSAS. As such, all the data modelling needs to happen in SSAS.

But you might be onto something there with splitting the column. I will try that out. However, the issue is that the milliseconds do not actually appear in SSAS (but they exist somewhere in the back as they are taken in consideration when doing calculations, distinct etc.).

[Anonymous]

let
    Source = "2019-06-20 06:02:55.667",
    SourceToDateTimeFormat = DateTime.FromText(Source)
in
    SourceToDateTimeFormat

Just tried this out of curiosity. The code above is giving the output "20-06-2019 06:02:55".