Forum Discussion
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...
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 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))
)
)