Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
Hi @amitchandak would you be able to help me here I saw some solutions of yours but it does not work here
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |