Last Date and Next Date.

[John343467]

Hi, 

I'm a little stuck with this one. 

I have the following table:

Name	Event	Startdate	Participated
John	Davis Cup	20.10.2020	No
John	French Open	15.07.2020	Yes
John	Wimbledon	19.06.2020	Yes
John	US Open	20.02.2021	No
Bill	Davis Cup	20.10.2020	Yes
Bill	French Open	15.07.2020	Yes
Bill	Wimbledon	19.06.2020	Yes
Bill	US Open	20.02.2021	No

And I would like the following result: 

Name	Last Tournament Participation	Next Tournament Participation
John	French Open - 15.07.2020	US Open - 20.02.2021
Bill	Davis Cup - 20.10.2020	US Open - 20.02.2021

I need to write the event name in the last and next participation column, add the date and check for a yes for participation for the past events. I started by creating a simple measure trying to work out the last participation with calculate (max) of the startdate column but I'm a bit stuck here. 

Thanks for any help. 

[littlemojopuppy · Accepted solution]

Worked this out in Excel/Power Pivot...

Here are the measures

Last Tournment Participation Date:=CALCULATE(
		LASTNONBLANK(
			'Tournament Participation'[Startdate],
			TRUE()
		),
		FILTER(
			ALLEXCEPT(
				'Tournament Participation',
				'Tournament Participation'[Name]
			),
			'Tournament Participation'[Participated] = TRUE()
		)
	)

Last Tournment Participation:=CALCULATE(
		FIRSTNONBLANK(
			'Tournament Participation'[Event],
			TRUE()
		),
		FILTER(
			ALLEXCEPT(
				'Tournament Participation',
				'Tournament Participation'[Name]
			),
			'Tournament Participation'[Startdate] = [Last Tournment Participation Date]
		)
	)

Next Tournament Date:=CALCULATE(
		MIN('Tournament Participation'[Startdate]),
		FILTER(
			ALL('Tournament Participation'),
			'Tournament Participation'[Startdate] >= TODAY()
		)
	)

Next Tournament:=CALCULATE(
		FIRSTNONBLANK(
			'Tournament Participation'[Event],
			TRUE()
		),
		FILTER(
			ALLEXCEPT(
				'Tournament Participation',
				'Tournament Participation'[Name]
			),
			'Tournament Participation'[Startdate] = [Next Tournament Date]
		)
	)

[v-yiruan-msft · Accepted solution]

Hi @John343467 ,

You can create two measures as below:

Last Tournament Participation = 
VAR _lastpdate =
    CALCULATE (
        MAX ( 'Events'[Startdate] ),
        FILTER (
            ALL ( 'Events' ),
            'Events'[Name] = MAX ( 'Events'[Name] )
                && 'Events'[Participated] = "Yes"
        )
    )
VAR _lastpevent =
    CALCULATE (
        MAX ( 'Events'[Event] ),
        FILTER (
            ALL ( 'Events' ),
            'Events'[Name] = MAX ( 'Events'[Name] )
                && 'Events'[Participated] = "Yes"
                && 'Events'[Startdate] = _lastpdate
        )
    )
RETURN
    CONCATENATE ( CONCATENATE ( _lastpevent, " - " ), _lastpdate )

Next Tournament Participation = 
VAR _lastNpdate =
    CALCULATE (
        MAX ( 'Events'[Startdate] ),
        FILTER (
            ALL ( 'Events' ),
            'Events'[Name] = MAX ( 'Events'[Name] )
                && 'Events'[Participated] = "No"
        )
    )
VAR _lastNpevent =
    CALCULATE (
        MAX ( 'Events'[Event] ),
        FILTER (
            ALL ( 'Events' ),
            'Events'[Name] = MAX ( 'Events'[Name] )
                && 'Events'[Participated] = "No"
                && 'Events'[Startdate] = _lastNpdate
        )
    )
RETURN
    CONCATENATE ( CONCATENATE ( _lastNpevent, " - " ), _lastNpdate )

Best Regards

[John343467]

@v-yiruan-msft Brilliant, thank you. Just a few little points.

1. I've noticed that I've some events in the future that have yes for Participation listed (it's a mistake in the data), how could I get around this so that for Last Tournament Participation, these events are not shown and only the ones in the past are shown? 

2. If I just wanted the event and not the date to show in the last event participation column, what should the DAX be for the last line. 

Thanks again, much appreciated. 

[littlemojopuppy · Accepted solution]

Worked this out in Excel/Power Pivot...

Here are the measures

Last Tournment Participation Date:=CALCULATE(
		LASTNONBLANK(
			'Tournament Participation'[Startdate],
			TRUE()
		),
		FILTER(
			ALLEXCEPT(
				'Tournament Participation',
				'Tournament Participation'[Name]
			),
			'Tournament Participation'[Participated] = TRUE()
		)
	)

Last Tournment Participation:=CALCULATE(
		FIRSTNONBLANK(
			'Tournament Participation'[Event],
			TRUE()
		),
		FILTER(
			ALLEXCEPT(
				'Tournament Participation',
				'Tournament Participation'[Name]
			),
			'Tournament Participation'[Startdate] = [Last Tournment Participation Date]
		)
	)

Next Tournament Date:=CALCULATE(
		MIN('Tournament Participation'[Startdate]),
		FILTER(
			ALL('Tournament Participation'),
			'Tournament Participation'[Startdate] >= TODAY()
		)
	)

Next Tournament:=CALCULATE(
		FIRSTNONBLANK(
			'Tournament Participation'[Event],
			TRUE()
		),
		FILTER(
			ALLEXCEPT(
				'Tournament Participation',
				'Tournament Participation'[Name]
			),
			'Tournament Participation'[Startdate] = [Next Tournament Date]
		)
	)

[John343467]

@littlemojopuppy Thank you very much. Just checking the last Tournament participation measure. For the last line, I get the message, cannot find name Last Tournment Participation Date. Thanks again. Much appreciated. 

[littlemojopuppy]

"Last Tournment Participation Date" is one of the measures from above...you added all of them?

[littlemojopuppy]

You don't want to use a column...use measures.  Calculated columns should be avoided.

Can't cut and paste a picture to work with the data.

[John343467]

Ok, updated the data to tables. Thanks. 

[littlemojopuppy]

You should be able to accomplish the most recently participated with LASTNONBLANK() filtering for where participated is true.  Next participated should be the name of the next tournament with the lowest date after today right?

Without data to work with hard to provide the exact measure

[John343467]

@littlemojopuppy Ok but the data is in the table above. Is it visible for you?

I started with this as a new column but this just gives me the last date in the table. CALCULATE ( MAX ('Table'[Startdate].[Date]), ALLEXCEPT( 'Table',Table [Name] ) )

Thanks.