Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Calculated column Measure for DateTime

Hi Experts   Here is my Calculated Column Measure  Hours per Job = (DATEDIFF(FACT_FSM_JOB_DATA[Start Time],FACT_FSM_JOB_DATA[Finish Time],MINUTE)/60)   The issue i am having is when the Job fin...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    If the field Start Date and Finish Date are both in the same day, you can change the formula of your calculated column [Hours per Job] as below if the field Finish Time may span one day to the next day... Please find the details in the attachment.

    Note: The part with red font is updated ones.

    Hours per Job =
    VAR _hours =
        DIVIDE (
            DATEDIFF (
                FACT_FSM_JOB_DATA[Start Time],
                IF (
                    'FACT_FSM_JOB_DATA'[Finish Time] < 'FACT_FSM_JOB_DATA'[Start Time],
                    'FACT_FSM_JOB_DATA'[Finish Time] + TIME ( 24, 0, 0 ),
                    'FACT_FSM_JOB_DATA'[Finish Time]
                ),
                MINUTE
            ),
            60,
            0
        )
    RETURN
        IF (
            'FACT_FSM_JOB_DATA'[Finish Time] < 'FACT_FSM_JOB_DATA'[Start Time],
            _hours + 24,
            _hours
        )

    Otherwise (if the field Start Date and Finish Date span multiple days), please provide more examples and calculation logic as well with screenshot of the final expected result. Thank you.

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

If the field Start Date and Finish Date are both in the same day, you can change the formula of your calculated column [Hours per Job] as below if the field Finish Time may span one day to the next day... Please find the details in the attachment.

Note: The part with red font is updated ones.

Hours per Job =
VAR _hours =
    DIVIDE (
        DATEDIFF (
            FACT_FSM_JOB_DATA[Start Time],
            IF (
                'FACT_FSM_JOB_DATA'[Finish Time] < 'FACT_FSM_JOB_DATA'[Start Time],
                'FACT_FSM_JOB_DATA'[Finish Time] + TIME ( 24, 0, 0 ),
                'FACT_FSM_JOB_DATA'[Finish Time]
            ),
            MINUTE
        ),
        60,
        0
    )
RETURN
    IF (
        'FACT_FSM_JOB_DATA'[Finish Time] < 'FACT_FSM_JOB_DATA'[Start Time],
        _hours + 24,
        _hours
    )

Otherwise (if the field Start Date and Finish Date span multiple days), please provide more examples and calculation logic as well with screenshot of the final expected result. Thank you.

Best Regards