Showing duration as HH:MM and subtotal

DAX DAX Calculations
1

I’m trying to replicate this report:

image
image1135×314 30.3 KB

And here’s my data:

Hours Data.xlsx (12.4 KB)

The hours column is the minutes worked. It doesn’t have to be DAX, Power Query is fine too.

The issue I’m running into is the user wants the data displayed in HH:MM, but it needs to subtotal as well.

I’ve tried having it in text format to get the “:” between, but that doesn’t sum.

I’ve tried it as a decimal but turning it into a time-like number e.g. 7.25 into 7.15 to represent 15 minutes, but then that doesn’t subtotal properly - I get 28.75 instead of 29.15 as you’d expect.

Is there anything I can do to achieve this?

Thanks,

2

Hello @jamie.bryan,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below are the DAX measures alongwith the screenshot of the final results provided for the reference -

The solution is provided in a “Two-fold format” i.e., one in the form of “Text” format and other one in the form of “Numerical” format so that you can use the numerical one for adding the numbers or showcasing them in a graphical format, if required.

A). Text Format Measures -

1). Calculation of Basic Hours -

Basic Hours - Harsh = 
VAR _Total_Seconds = 
SUMX( 
    FILTER( Data ,
        Data[HRSCODE] = "Basic Hours" ) , 
    Data[MINUTES] ) * 60

VAR _Days =
TRUNC(
    DIVIDE(
        DIVIDE(
            _Total_Seconds , 
            3600 , 
            0 ) , 
        24 ,
        0 ) )

VAR _Hours = 
TRUNC(
    DIVIDE(
        _Total_Seconds - _Days * 3600 * 24 ,
        3600 ,
        0 ) )

VAR _Minutes = 
TRUNC(
    DIVIDE(
        MOD(
            _Total_Seconds , 3600 ) , 
        60 ,
        0 ) )

VAR _Seconds = 
MOD(
    _Total_Seconds , 60 )

VAR _Results = 
IF( ISBLANK( _Total_Seconds ) , 
        BLANK() , 
    IF( _Days >= 1 , ( _Days * 24 ) + _Hours , _Hours ) & ":" & 
    IF( _Minutes < 10 , "0" & _Minutes , _Minutes ) & ":" & 
    IF( _Seconds < 10 , "0" & _Seconds , _Seconds ) )

RETURN
_Results

2). Calculation of O/T @ Time & Half -

O/T @ Time & Half - Harsh = 
VAR _Total_Seconds = 
SUMX( 
    FILTER( Data ,
        Data[HRSCODE] = "O/T @ Time & Half" ) , 
    Data[MINUTES] ) * 60

VAR _Days =
TRUNC(
    DIVIDE(
        DIVIDE(
            _Total_Seconds , 
            3600 , 
            0 ) , 
        24 ,
        0 ) )

VAR _Hours = 
TRUNC(
    DIVIDE(
        _Total_Seconds - _Days * 3600 * 24 ,
        3600 ,
        0 ) )

VAR _Minutes = 
TRUNC(
    DIVIDE(
        MOD(
            _Total_Seconds , 3600 ) , 
        60 ,
        0 ) )

VAR _Seconds = 
MOD(
    _Total_Seconds , 60 )

VAR _Results = 
IF( ISBLANK( _Total_Seconds ) , 
        BLANK() , 
    IF( _Days >= 1 , ( _Days * 24 ) + _Hours , _Hours ) & ":" & 
    IF( _Minutes < 10 , "0" & _Minutes , _Minutes ) & ":" & 
    IF( _Seconds < 10 , "0" & _Seconds , _Seconds ) )

RETURN
_Results

B). Numerical Format Measures -

1). Calculation of Basic Hours -

Basic Hours - Hours - Harsh = 
VAR _Total_Seconds = 
SUMX( 
    FILTER( Data ,
        Data[HRSCODE] = "Basic Hours" ) , 
    Data[MINUTES] ) * 60

VAR _Days =
TRUNC(
    DIVIDE(
        DIVIDE(
            _Total_Seconds , 
            3600 , 
            0 ) , 
        24 ,
        0 ) )

VAR _Hours = 
TRUNC(
    DIVIDE(
        _Total_Seconds - _Days * 3600 * 24 ,
        3600 ,
        0 ) )

VAR _Minutes = 
TRUNC(
    DIVIDE(
        MOD(
            _Total_Seconds , 3600 ) , 
        60 ,
        0 ) )

VAR _Seconds = 
MOD(
    _Total_Seconds , 60 )

VAR _Results = 
IF( ISBLANK( _Total_Seconds ) , BLANK() , 
IF( _Days < 1 , ( _Days * 24 ) + _Hours + ( _Minutes / 60 ) ,
IF( _Days >= 1 , ( _Days * 24 ) + _Hours + ( _Minutes / 60 ) ) ) )

RETURN
_Results

2). Calculation of O/T @ Time & Half -

O/T @ Time & Half - Hours - Harsh = 
VAR _Total_Seconds = 
SUMX( 
    FILTER( Data ,
        Data[HRSCODE] = "O/T @ Time & Half" ) , 
    Data[MINUTES] ) * 60

VAR _Days =
TRUNC(
    DIVIDE(
        DIVIDE(
            _Total_Seconds , 
            3600 , 
            0 ) , 
        24 ,
        0 ) )

VAR _Hours = 
TRUNC(
    DIVIDE(
        _Total_Seconds - _Days * 3600 * 24 ,
        3600 ,
        0 ) )

VAR _Minutes = 
TRUNC(
    DIVIDE(
        MOD(
            _Total_Seconds , 3600 ) , 
        60 ,
        0 ) )

VAR _Seconds = 
MOD(
    _Total_Seconds , 60 )

VAR _Results = 
IF( ISBLANK( _Total_Seconds ) , BLANK() , 
IF( _Days < 1 , ( _Days * 24 ) + _Hours + ( _Minutes / 60 ) ,
IF( _Days >= 1 , ( _Days * 24 ) + _Hours + ( _Minutes / 60 ) ) ) )

RETURN
_Results

Final Results
Final Results1199×354 8.49 KB

I’m also attaching the working of the PBIX file as well as providing a link of a post where I had provided a similar solution few days back.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Time in HH:MM Format and Sub-Totals - Harsh.pbix (64.6 KB)

https://forum.enterprisedna.co/t/time-duration-is-not-adding-correctly/30220/2

2 Likes
3

I thought I remembered seeing something similar the other day, couldn’t find it though sorry!

Thanks as always Harsh, it works a treat.

4

Hello @jamie.bryan,

You’re Welcome! :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh