DAX Calculated column Measure for DateTime

Question

Hi Experts&nbsp;Here is my Calculated Column Measure&nbsp;Hours per Job = (DATEDIFF(FACT_FSM_JOB_DATA[Start Time],FACT_FSM_JOB_DATA[Finish Time],MINUTE)/60)&nbsp;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..&nbsp;See sample data image....&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
If the field&nbsp;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&nbsp;per&nbsp;Job&nbsp;=VAR&nbsp;_hours&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATEDIFF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FACT_FSM_JOB_DATA[Start&nbsp;Time],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'FACT_FSM_JOB_DATA'[Finish&nbsp;Time]&nbsp;&lt;&nbsp;'FACT_FSM_JOB_DATA'[Start&nbsp;Time],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'FACT_FSM_JOB_DATA'[Finish&nbsp;Time]&nbsp;+&nbsp;TIME&nbsp;(&nbsp;24,&nbsp;0,&nbsp;0&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'FACT_FSM_JOB_DATA'[Finish&nbsp;Time]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MINUTE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;60,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'FACT_FSM_JOB_DATA'[Finish&nbsp;Time]&nbsp;&lt;&nbsp;'FACT_FSM_JOB_DATA'[Start&nbsp;Time],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;_hours&nbsp;+&nbsp;24,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;_hours&nbsp;&nbsp;&nbsp;&nbsp;)

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

Forum Discussion

DAX Calculated column Measure for DateTime

2 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Converting Epoch Datetime into regular datetime format

Measure or Calculated Column

Convert DateTime to Date