cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Calculation function only returning minutes

I have created a New Column called Downtime.  It is supposed to take the total Downtime Minutes and break it into Days, Hours, and Minutes.  It is pulling the remainder minutes properly but is not adding the hours beforehand.

 

(Source Column)
Downtime Minutes                                         Downtime
99.00                                                                     0 day  00 hours  33 Min

 

This should read 0 day  01 hours  33 Min, but is missing the hour.

Below is the DAX code:

Downtime =
VAR dayNo =
    INT ( EventLogDetail[DowntimeMinutes] / 1440 )
VAR hourNo =
    INT ( MOD ( EventLogDetail[DowntimeMinutes], 1440 ) / 60 )
VAR minuteNo =
    MOD ( MOD ( EventLogDetail[DowntimeMinutes], 1440 )60 )
RETURN
    dayNo & " day  "
        & FORMAT ( hourNo, "#00" )
        & "  hours "
        & FORMAT ( minuteNo, "#00" )
        & " Min"

Any help would be greatly appreciated.

Thanks,

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Calculation function only returning minutes

Can you post some sample data of your downtime table?  The issue might be that you need to specify some type of aggregation for your downtime minutes column, rather than the raw column reference you are providing.  In my example, I just hard coded in the number 99, but if you have multiple values in your downtime minutes column, it wont work by just giving the column name.

View solution in original post

13 REPLIES 13
Highlighted
Microsoft
Microsoft

Re: Calculation function only returning minutes

Why not change your hourNo variable to this:

 

hourNo = ROUNDDOWN(EventLogDetail[DowntimeMinutes]/60, 0)

Highlighted
Microsoft
Microsoft

Re: Calculation function only returning minutes

I tried both yours and mine and they both work for me.  when put into a card I get "0 days 01 hours 39 mins" for your 99 minutes value.

Highlighted
Helper I
Helper I

Re: Calculation function only returning minutes

Thanks for the reply to my post.

 

Can you explain what you mean when you say "when put into a card"  Maybe I can try that to help me understand why this is not working.

Highlighted
Microsoft
Microsoft

Re: Calculation function only returning minutes

I am referring to the Card visual:

 

InkedCapture_LI.jpg

Highlighted
Helper I
Helper I

Re: Calculation function only returning minutes

so strange that this is not working for me.  Do you think it might have something to do with the datatype of the source?

Highlighted
Microsoft
Microsoft

Re: Calculation function only returning minutes

What is the source?  Are you using DirectQuery or Import?

Highlighted
Helper I
Helper I

Re: Calculation function only returning minutes

Datatype is Decimal Number and this is an import

Highlighted
Microsoft
Microsoft

Re: Calculation function only returning minutes

So you have a field in your data that gives downtime minutes, and you tried putting your measure into a Card?

 

Here is my result (using 99 minutes as your example showed):

 

Capture.PNG

Highlighted
Microsoft
Microsoft

Re: Calculation function only returning minutes

Can you post some sample data of your downtime table?  The issue might be that you need to specify some type of aggregation for your downtime minutes column, rather than the raw column reference you are providing.  In my example, I just hard coded in the number 99, but if you have multiple values in your downtime minutes column, it wont work by just giving the column name.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors