topic DAX for Time in DAX Commands and Tips

DAX for Time

MrBlackEyE31 — Tue, 11 Jun 2024 16:34:36 GMT

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.

Re: DAX for Time

lbendlin — Wed, 12 Jun 2024 00:41:56 GMT

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

Re: DAX for Time

Anonymous — Fri, 14 Jun 2024 07:48:34 GMT

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