Forum Discussion
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
- lbendlinSuper User
You need to do all your math with decimals, and then at the very last minute reformat it to display as a duration.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - AnonymousNot applicable
Hi MrBlackEyE31 ,
An update on this? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng