Event calendar: Next event and previous event

[asn4293]

I am stuck in building the event calendar but in concise view. It should show me by stakeholder what was the last event and what will be the next event for the respective stakeholder. Below you will find the solution how it should look like. 

Stakeholder group	Current Engagement level	Last Event	Last Event Date	Next Event	Next Event date
A	1	Board Meeting	20.11.2022	Board brainstorming	01.01.2023
B	1	Leadership meeting	19.11.2022	Leadership comms	02.01.2023
C	2	Offsite hangout	20.10.2022	On site discussion	03.01.2023
D	2	Strategic communication	15.11.2022	Global comms	15.01.2023

And the data looks like this 

Event	Stakeholder Group	Start Date	End Date
Board Meeting	A	21. Okt 22	21. Okt 22
Board talks	A	04. Okt 22	10. Nov 22
Board Meeting	A	20. Nov 22	20. Nov 22
Board brainstorming	A	01. Jan 23	01. Jan 23
Board Meeting	A	04. Jan 23	04. Jan 23
Leadership Meeting	B	19. Nov 22	19. Nov 22
Leadership talks	B	10. Nov 22	15. Nov 22
Leadership comms	B	02.01.2023	02.01.2023

So in theory what I need a formula which can give 

1) Next event definition:
Where End date is greater than today and minimum of End date date based on stakeholder

2) Last event definition:
Where End date is less than today and maximum of end date based on stakeholder 

[asn4293]

@v-rzhou-msft  Thank you so much 

[v-rzhou-msft · Accepted solution]

Hi @asn4293 ,

Here I create a sample to have a test.

Data model:

Last Event = 
CALCULATE(MAX('Table'[Event]),FILTER('Table','Table'[End Date] = [Last Event Date]))

Last Event Date =
CALCULATE (
    MAX ( 'Table'[End Date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Stakeholder Group] = MAX ( 'Table (2)'[Stakeholder group] )
            && 'Table'[End Date] <= TODAY ()
    )
)

Next Event = 
CALCULATE(MAX('Table'[Event]),FILTER('Table','Table'[End Date] = [Next Event Date]))

Next Event Date =
CALCULATE (
    MIN ( 'Table'[End Date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Stakeholder Group] = MAX ( 'Table (2)'[Stakeholder group] )
            && 'Table'[End Date] > TODAY ()
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[asn4293]

Hi @amitchandak would you be able to help me here I saw some solutions of yours but it does not work here