Round hours difference to nearest .25 Help

[Nicci]

Hi All,
I am stumped. I have a column that does this:

TotalHours = DATEDIFF(HearingsScheduled[StartTime], HearingsScheduled[EndTime], MINUTE) /60

This outputs the number of hours between start/end time, output example is this: 0.23, 4.02, 2.95.

I want this to be more readable to users as hours and quarter minutes and I have a business rule to round:

If it's 7 minutes or more then round up to the nearest .15 increment. So example, if the time is 1.22 that should round to 1.25, but if it was 1.21, that should round to 1.15. another example is if the time is 4.07 it should round to 4.15, but 4.06 should be 4.00.

How do I do this from the column I created that gives me the straight difference in minutes / 60 which gives me a decimal number.

[ryan_mayu · Accepted solution]

@Nicci 

sometimes you want to roundup, sometimes you want to rounddown. Then you need to use switch to list all the senarios. 

pls see the DAX below

Column =

var _min=DATEDIFF('Table'[starttime],'Table'[endtime],MINUTE)

var _hour=int(_min/60)

var _min2=mod(_min,60)

var _neaeast = SWITCH(TRUE(),_min2<14,0.15,_min2<28,0.25)

return _hour+_neaeast

you can try to update the coding in the SWTICH function to meet your requirement.

[Anonymous · Accepted solution]

Hi @Nicci ,

I create a table and a calculated column as you mentioned.

Then I think you can create another calculated column and use this DAX code.

RoundedHours = 
VAR TotalMinutes = DATEDIFF(HearingsScheduled[StartTime], HearingsScheduled[EndTime], MINUTE)
VAR DecimalHours = TotalMinutes / 60
VAR MinutesPart = MOD(TotalMinutes, 60)
VAR RoundedMinutes = 
    SWITCH(
        TRUE(),
        MinutesPart >= 7 && MinutesPart < 22, 0.25,
        MinutesPart >= 22 && MinutesPart < 37, 0.50,
        MinutesPart >= 37 && MinutesPart < 52, 0.75,
        MinutesPart >= 52, 1,
        0
    )
RETURN
    INT(DecimalHours) + RoundedMinutes

Best Regards

Yilong Zhou

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

[ryan_mayu · Accepted solution]

@Nicci 

sometimes you want to roundup, sometimes you want to rounddown. Then you need to use switch to list all the senarios. 

pls see the DAX below

Column =

var _min=DATEDIFF('Table'[starttime],'Table'[endtime],MINUTE)

var _hour=int(_min/60)

var _min2=mod(_min,60)

var _neaeast = SWITCH(TRUE(),_min2<14,0.15,_min2<28,0.25)

return _hour+_neaeast

you can try to update the coding in the SWTICH function to meet your requirement.