Forum Discussion

MrBlackEyE31's avatar
MrBlackEyE31
Frequent Visitor
2 years ago

DAX for Time

Hello,

 

I am trying to gather metrics on completion times in Power BI. I want to see this in Hours and Minutes.

 

I have tried a few solutions that "work" but don't work to what we are trying to achieve. 

 

I am getting the time as a decimal number by subracting a created on date and a resolution date. 

Example: A case Created on 6/8 at 6am and Resolved on 6/9 at 6pm would give me a 1.5 for 1.5 days 

 

The first solution I did and really liked was a dax like this
 CaseResTime =
VAR ElapsedTime = Case time (which is my DateDiff column)

 

VAR days = INT(ElapsedTime)

 

VAR _hrs = (ElapsedTime - days)*24

 

VAR hrs = INT(_hrs)

 

VAR mins = ROUNDD((_hrs - hrs)*60,0)

 

RETURN

 

days & "D" & hrs & "H" & mins & "M"

This looked great easy to read and explanitory 1D12H28M   Something like that but my problem was I couldn't average it because it isn't a "Number"

 

I switched my thinking here and want to get it to now just be a decimal numeber (Hours).(Mins) so I wrote this DAX Statement

 

 CaseResTime =
VAR ElapsedTime = Case time (which is my DateDiff column)

 

VAR days = INT(ElapsedTime)

 

VAR _hrs = (ElapsedTime - days)*24

 

VAR dayshrs = days *24

 

VAR CaseHours = dayshrs + _hrs

 

VAR WholeCaseHr = INT(CaseHours)

 

VAR Minutes = (CaseHours - WholeCaseHr) *0.01

 

VAR AcurateMin = Minutes*60

 

RETURN

 

WholeCaseHr + AccurateMin

 

This is giving me what I want like 1.30 is 1 hour 30 min     but when I average this it gets messy. Ideally you wouldnt see above .59 on anything because that would be 59 minutes but averaging things out some  employees have outcomes saying 3.75

 

Please let me know if there are any easier ways to achieve what I am trying to do.

Hopefully I gave enough details.  Thank you for looking into this question.

2 Replies