Issue with rounding of time to every 6 hours in a day in power bi

[sindhusabbineni]

I want to group fields as per the datetime which is a field in the table and is in the format (9/27/2019 00:00:00 AM). My requirement is to group fields for every 6 hours, specifically from 12 AM to 6 AM, 6 AM to 12 PM, 12 PM to 6 PM, 6 PM to 12 AM. I am trying to use the below DAX formula.

roundedhour = MROUND ( UserAPI[dy_startTime], TIME ( 4, 0, 0 ) ) + TIME ( 0, 0, 0 )

But, the time is being rounded for 3 AM to 9 AM, 9 AM to 3 PM, 3 PM to 9 PM, 9 PM to 3 AM which is not what i am looking for. Can you please let me know how to get the required time blocks.

[v-yuta-msft]

@sindhusabbineni ,

---

@sindhusabbineni wrote:

I want to group fields as per the datetime which is a field in the table and is in the format (9/27/2019 00:00:00 AM). My requirement is to group fields for every 6 hours, specifically from 12 AM to 6 AM, 6 AM to 12 PM, 12 PM to 6 PM, 6 PM to 12 AM. I am trying to use the below DAX formula.

roundedhour = MROUND ( UserAPI[dy_startTime], TIME ( 4, 0, 0 ) ) + TIME ( 0, 0, 0 )

But, the time is being rounded for 3 AM to 9 AM, 9 AM to 3 PM, 3 PM to 9 PM, 9 PM to 3 AM which is not what i am looking for. Can you please let me know how to get the required time blocks.

---

Could you please share the sample data and clarify more details for further analysis? 

Regards,

Jimmy Tao

[sindhusabbineni]

@v-yuta-msft 

TestEnd_Time	Test Endtime2h
11/10/2019 6:52:12AM	11/10/2019 6:00AM
11/10/2019 7:12:12AM	11/10/2019 8:00AM

Formula for Test Endtime2h = MROUND(JSAutomation_loading_temp[TestEnd_Time], TIME(2,0,0)) + TIME(0,0,0). But I need 6-8 as 6 AM block. Please let me know.