DAX Calculated column Measure for DateTime

[Anonymous]

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 finish time is the next day and say 00:04:00 in th morning i am getting (-22.4)hrs which is incorrect if the job start time was 21:49:00...How could i adjust my measure to show the time as positive minutes worked and the correct minutes when we roll over into the next day..

See sample data image....

[Anonymous · Accepted solution]

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

[Greg_Deckler]

@Anonymous Perhaps something like:

Hours per Job = 
  IF(
    [Finish Time] > [Start Time],
    (DATEDIFF(FACT_FSM_JOB_DATA[Start Time],FACT_FSM_JOB_DATA[Finish Time],MINUTE)/60),
    (23/24 + 59/60 + 59/60/60 + 1/24/60/60) - [Start Time] + [Finish Time]
  )

or

  IF(
    [Finish Time] > [Start Time],
    (DATEDIFF(FACT_FSM_JOB_DATA[Start Time],FACT_FSM_JOB_DATA[Finish Time],MINUTE)/60),
    ABS(DATEDIFF(FACT_FSM_JOB_DATA[Start Time]+[StartDate],FACT_FSM_JOB_DATA[Finish Time]+[Finish Date],MINUTE)/60))
  )
  )