Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

undefined

Hi there! I need some assistance with calculating a time difference between values in the same column. I have seen some other posts on the same topic, but they are not similar enough to my issue that...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

Create calculate column.

 

Time Difference =

VAR CurrentRowDate = 'Table'[EVENTDATE_LOCAL]

VAR CurrentVOYNUM = 'Table'[VOYNUM]

VAR CurrentPosition = 'Table'[Position_Custom]

VAR DepartureDate =

    CALCULATE(

        MIN('Table'[EVENTDATE_LOCAL]),

        FILTER(

            'Table',

            'Table'[VOYNUM] = CurrentVOYNUM

            && 'Table'[Position_Custom] = CurrentPosition

            && 'Table'[REPORT_TYPE] = "Departure"

            && 'Table'[EVENTDATE_LOCAL] > CurrentRowDate

        )

    )

RETURN

IF('Table'[REPORT_TYPE] = "Arrival" && NOT(ISBLANK(DepartureDate)),

    DATEDIFF(CurrentRowDate,DepartureDate,HOUR) / 24,

    BLANK()

)

 

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you Clara

    Unfortunately, I have a DirectQuery model and get the error message that the function CALCULATE is not allowed. I also tried to use the formula as a measure but it seems the issue is then that a single value for column 'EVENTDATE_LOCAL' in 'Table' cannot be determined. Not quite sure how to proceed.