Round hours difference to nearest .25 Help

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.

 

 

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.