Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX

Hi, I am truly sorry if this question has been asked before but I can't seem to find the answer in the internet.

 

I have a table in a direct query mode that is populated everytime someone send a request :

 

From the Event Date column, I want to create a calculated column or measure that compute the duration between current date and previous entry date like this :

 

Vessel IDEvent DateDuration
96797517/2/20210
967975119/2/202112
967975125/2/20216
931626812/2/20210
931626820/2/20218
970454415/2/20210

 

This is what I have right now:

 

Duration = 
VAR CurrentDate = ONOFF[EventDate]
VAR PreviousDate =
    CALCULATE (
        MAX ( ONOFF[EventDate]),
        FILTER (ALLSELECTED(ONOFF),
        ONOFF[VesselId] = ONOFF[VesselId] & ONOFF[EventDate] < CurrentDate
        )
    )
RETURN
    CurrentDate - PreviousDate

 

Can anyone help me? 

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new measure like

     

    Duration =
    datediff( max(Table[Event Date]), calculate(max(Table[Event Date]), filter(allselected(Table), Table[Vessel ID] = max(Table[Vessel ID]) && Table[Event Date] < max(Table[Event Date]))), day)

     

     

    or

     


    Duration =
    sumx(ADDCOLUMNS(summarize(Table,Table[Vessel ID],[Event Date]), "_1" datediff( max(Table[Event Date]), calculate(max(Table[Event Date]), filter(allselected(Table), Table[Vessel ID] = max(Table[Vessel ID]) && Table[Event Date] < max(Table[Event Date]))), day)),[_1])