Dax round of datetime

[Anonymous]

Hi all,

Working in Live Connect (so I cannot seperate columns), I have a datetime column in the following 24H format: "YYYY-MM-DD HH:MM:SS.ttt", e.g. "2019-05-13 15:18:40.963".

I'm trying to make a measure summing the sales in the previous full hour, assuming I have a measure returning the max value in that column (max(Updatetime)).

So in the about example, the sum of sales will be for the time between "2019-05-13 14:00:00.000" (bottom) and "2019-05-13 14:59:59.999 (top)".

My problem is finding the top and bottom boundries using dax and keeping them in the same datetime format.

Any assistance would be welcome.

Thank you!

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the below DAX expression, you should be able to adjust it to what you need and then add extra calculation to obtain sum .

Your Measure = 
VAR dateTime = NOW() -- Your max(Updatetime)
VAR TimeEnd = DATEVALUE(dateTime) + TIME(INT(TIMEVALUE(dateTime) * 24), 0, 0)
VAR TimeStart = TimeEnd - TIME(1, 0, 0)
RETURN TimeStart & " - " TimeEnd

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This works perfectly, thank you!

much more elegant than I initially thought.
I tried using "left", "datepart", "format", "datevalue" and other workarounds and failed.

So thanks a lot!