Date in Time Column 12/30/...

[2beefd]

Hi all,

I am trying to create a graph using time/date data to show the sum by hour of first responses to tickets. To do this, I removed the date from the time column and rounded to the nearest half hour, accounting for null values, using the following expression: 

First Response Time = IF(ISBLANK(Metrics[SLA First Response Date/Time]), MROUND("00:00:00", "00:30:00"), MROUND(FORMAT(Metrics[SLA First Response Date/Time], "hh:mm:ss"), "00:30:00"))

This returns a column that only displays time values (even if the data type is set to date/time), but when the data type is set to Time and a graph is made, the X axis formats the time as if it had a date:

My first assumption is that DAX sets the datatype to Time/Date by default but I am unsure how to change it such that only the time values are displayed on the graph. Any suggestions on this would be appriciated

[2beefd · Accepted solution]

After doing some digging, I was able to determine the cause of this issue. The problem comes from the MROUND function, which when rounding the time would round both up and down to 12:00:00 AM. When rounding up, the time is technically being rounded to 24:00:00, or 12:00:00 AM on 12/31/1899. To solve this issue, I only rounded in the downward direction to the nearest half hour:

First Response Time = 
VAR Hour =  HOUR(Metrics[SLA First Response Date/Time])
VAR Minute = MINUTE(Metrics[SLA First Response Date/Time])

RETURN IF(Minute<30,TIME(Hour,0,0),TIME(Hour,30,0))

[v-luwang-msft]

Hi @2beefd ,

Try creating a calculation column to convert the value to text:

Column = FORMAT('Calendar'[Date] , "hh:nn:ss AM/PM")

Wish it is helpful for you!

Best Regards

Lucien

[Fowmy]

@2beefd 
If the data type is time and you have applied the formatting as "h:nn:ss AM/PM" then you should only see the time values in the X axis.