Convert Text from a measure into an Integer

[Harsh]

Hello @krampit,

Thank You for posting your query onto the Forum.

In order to have the hours based on a “Numerical” format. Below are the measures provided for the reference -

1. Regular Hours - Harsh - Conversion = 
VAR _Regular_Hours = 
IF(

    SELECTEDVALUE( Sales[Date Time Start] ) < SELECTEDVALUE( Sales[Date Time End] ) ,

    IF(

        SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 8 , 0 , 0 ) &&
        SELECTEDVALUE( Sales[Date Time End] ) <= TIME( 18 , 0 , 0 ) , 

        ROUNDUP(
            DIVIDE(
                DATEDIFF(
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
                    MINUTE ) ,
                60 , 
                0 ) ,
            2
        ) ,

        IF(

            SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 8 , 0 , 0 ) &&
            SELECTEDVALUE( Sales[Date Time Start] ) <= TIME( 18 , 0 , 0 ) , 

            ROUNDUP(
                DIVIDE(
                    DATEDIFF(
                        SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
                        SELECTEDVALUE( Sales[Date] ) + TIME( 18 , 0 , 0 ) , 
                        MINUTE ) , 
                    60 , 
                    0 ) , 
                2
            ) ,

            IF(

                SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 8 , 0 , 0 ) &&
                SELECTEDVALUE( Sales[Date Time End] ) <= TIME( 18 , 0 , 0 ) , 

                ROUNDUP(
                    DIVIDE(
                        DATEDIFF(
                            SELECTEDVALUE( Sales[Date] ) + TIME( 8 , 0 , 0 ) ,
                            SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
                            MINUTE ) , 
                        60 , 
                        0 ) , 
                    2
                )
            )
        )
    )
)

VAR _Results = 
IF(

    _Regular_Hours < 0 , 

    _Regular_Hours * -1 , 

    _Regular_Hours )


RETURN
_Results

3. Evening Hours - Harsh - Conversion = 
VAR _Evening_Hours =
IF(
    
    SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 18 , 0 , 0 ) &&
    SELECTEDVALUE( Sales[Date Time Start] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,

    ROUNDUP(
        DIVIDE(
            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) ,
                SELECTEDVALUE( Sales[Date] ) + ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) , 
                MINUTE ) , 
            60 , 
            0 ) ,
        2
    ) ,

    IF(

        SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 18 , 0 , 0 ) &&
        SELECTEDVALUE( Sales[Date Time End] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,

        ROUNDUP(
            DIVIDE(
                DATEDIFF(
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) ,
                    SELECTEDVALUE( Sales[Date] ) + TIME( 18 , 0 , 0 ) , 
                    MINUTE ) , 
                60 , 
                0 ) , 
            2
        )
    )
)

VAR _Results =
IF(
    
    _Evening_Hours < 0 , 

    _Evening_Hours * -1 , 

    _Evening_Hours )


RETURN
_Results

5. Night Hours - Harsh - Conversion = 
VAR _Night_Hours = 
IF(
    
    SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 0 , 0 , 0 ) &&
    SELECTEDVALUE( Sales[Date Time Start] ) < TIME( 8 , 0 , 0 ) ,

    ROUNDUP(
        DIVIDE(
            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) ,
                SELECTEDVALUE( Sales[Date] ) + TIME( 8 , 0 , 0 ) , 
                MINUTE ) , 
            60 , 
            0 ) ,
        2
    ) , 

    IF(

        SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 0 , 0 , 0 ) &&
        SELECTEDVALUE( Sales[Date Time End] ) < TIME( 8 , 0 , 0 ) ,

        ROUNDUP(
            DIVIDE(
                DATEDIFF(
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) ,
                    SELECTEDVALUE( Sales[Date] ) + TIME( 0 , 0 , 0 ) , 
                    MINUTE ) , 
                60 , 
                0 ) , 
            2
        )
    )
)

VAR _Results = 
IF(

    _Night_Hours < 0 , 

    _Night_Hours * -1 , 

    _Night_Hours )


RETURN
_Results

7. Total Hours - Harsh - Conversion = 
IF( 
    SELECTEDVALUE( Sales[Date Time End] ) > SELECTEDVALUE( Sales[Date Time Start] ) , 

    DIVIDE(
        DATEDIFF(
            SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
            SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
            MINUTE ) ,
        60 , 
        0 ) ,

    ROUNDUP(
        DIVIDE(
            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
                SELECTEDVALUE( Sales[Date] ) + TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) , 
                MINUTE ) +

            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + TIME( 0 , 0 , 0 ) , 
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
                MINUTE ) ,
            60 , 
            0 
        ) ,
    0 )
)

Once the “Main Measures” are written, write the small measures for each of them to get the “Grand Totals” as well. Below are the measures provided for the reference -

2. Regular Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [1. Regular Hours - Harsh - Conversion] ) , 
    [@Totals]
)

4. Evening Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [3. Evening Hours - Harsh - Conversion] ) , 
    [@Totals]
)

6. Night Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [5. Night Hours - Harsh - Conversion] ) , 
    [@Totals]
)

8. Total Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [7. Total Hours - Harsh - Conversion] ) ,
    [@Totals]
)

After writing all these measures, you’ll be able to see the desired results. Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file as well as providing a link below for the reference purposes pertaining to the topic - “How to Fix Incorrect Totals” which was created by our expert @Greg.

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

Thanks and Warm Regards,
Harsh

Shift Hours - Numerical Format - Harsh.pbix (30.5 KB)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…