DateDiff of same column, but only look into the future once

[michael_knight]

Hi All,

I'm trying to a datediff of the same column but only looking into the future once, the data is below. I created a measure which gives me the Weeks Between, but I don't want to include the top two as I only want to look at one date in the future

Interview Date	Status	Weeks between attended int	Expected Outcome
16/03/2022	Not Known	16
22/12/2021	Not Known	4
08/12/2021	Not Known	2	2
26/11/2021	Attended	4	4
19/11/2021	Failed to Attend	3	3
11/11/2021	Failed to Attend	2	2
05/11/2021	Failed to Attend	1	1
28/10/2021	Attended

Weeks between attended int = 
VAR temp =
    TOPN (
        1,
        FILTER (
            Interview,
            Interview[ID] = EARLIER ( Interview[ID] )
                && Interview[Interview Date] < EARLIER ( Interview[Interview Date] ) && Interview[Status] = "Attended"
        ),
        Interview[Interview Date], DESC
    )


RETURN
 DATEDIFF ( MINX ( temp, Interview[Interview Date] ), Interview[Interview Date], week )

Thanks,

Mike

[v-shex-msft · Accepted solution]

HI @michael_knight,

It seems like you want to calculate the datediff until the first future date, right? If that is the case, you can add a variable to get the first future date and if statement to compare the current date with that to prevent calculation on the following date ranges.

Weeks diff =
VAR _firstfuture =
    MINX ( FILTER ( Interview, [Interview Date] > TODAY () ), [Interview Date] )
VAR _nextDate =
    MINX (
        FILTER ( Interview, [Interview Date] > EARLIER ( Interview[Interview Date] ) ),
        [Interview Date]
    )
RETURN
    IF (
        Interview[Interview Date] <= _firstfuture,
        DATEDIFF ( Interview[Interview Date], _nextDate, WEEK )
    )

Regards,

Xiaoxin Sheng

[amitchandak]

@michael_knight , a new column

Weeks between attended int =
VAR temp =
MAXX (
FILTER (
Interview,
Interview[ID] = EARLIER ( Interview[ID] )
&& Interview[Interview Date] < EARLIER ( Interview[Interview Date] ) && Interview[Status] = "Attended"
),
Interview[Interview Date], DESC
)

RETURN
DATEDIFF ( MIN ( temp, Interview[Interview Date] ), Interview[Interview Date], week )

a new measure

Weeks between attended int =
VAR temp =
MAXX (
FILTER (
allselected(Interview),
Interview[ID] = MAx( Interview[ID] )
&& Interview[Interview Date] < MAx( Interview[Interview Date] ) && Interview[Status] = "Attended"
),
Interview[Interview Date], DESC
)

RETURN
DATEDIFF ( temp , min(Interview[Interview Date]), week )

[michael_knight]

Hi @amitchandak 

Thanks for the reply, I tried the column one you sent and it didn't work. There is a red line under 

'Interview[Interview Date], DESC'

Thanks

[amitchandak]

@michael_knight , sorry my bad try

a new column

Weeks between attended int =
VAR temp =
MAXX (
FILTER (
Interview,
Interview[ID] = EARLIER ( Interview[ID] )
&& Interview[Interview Date] < EARLIER ( Interview[Interview Date] ) && Interview[Status] = "Attended"
),
Interview[Interview Date]
)

RETURN
DATEDIFF ( MIN ( temp, Interview[Interview Date] ), Interview[Interview Date], week )

a new measure

Weeks between attended int =
VAR temp =
MAXX (
FILTER (
allselected(Interview),
Interview[ID] = MAx( Interview[ID] )
&& Interview[Interview Date] < MAx( Interview[Interview Date] ) && Interview[Status] = "Attended"
),
Interview[Interview Date]
)

[michael_knight]

Thanks, tried it out and it gives the same result of the measure that I've got unfortunatly