2023 Week 16 of Preppin' Data Challenges looked at the variation of the day in which Easter Sunday falls on. Turns out this is based on the full moon. As defined by the Royal Museums Greenwich, Easter is the first Sunday after the full moon that occurs on or after the spring equinox (the point where days and nights are roughly equal in length).
The aim of the output is to identify which full moons are the closest to Easter Sunday and see the variation in the days between the full moon and Easter Sunday.
Inputs
- All Easter Sundays (date)
- All Full Moons (day, date, and time)
Easter Sunday, and therefore the corresponding full moon, occurs around April/March. Joining the datasets together using the following two clauses will return all the full moons that occurred before the Easter Sunday of the same year.
Before we can return the full moon closest to Easer Sunday the difference in days between the full moon and Easter Sunday had to be calculated using the following calculation:
DATEDIFF('day',[Full Moon Date],[Easter Sunday])
From here, I found two methods that would return the full moon date closest to Easter Sunday.
1) Using Rank
Creating a new field with a rank calculation of the difference in days between the full moons and Easter Sunday.
The date could then be filtered to only include rows with a rank of 1, which denotes the full moon closest to the Easter Sunday date.
2) Using FIXED LOD
The same above can be achieved using a FIXED LOD by finding the MIN of the difference in days as shown below as a new field called 'Fewest Days'.
Once again, a filter calculation can be used to only show rows where days between full moon and Easter were equal to 'Fewest Days' which would show the full moon closest to the Easter Sunday date.
Aggregate
The data is then aggregated as below:
To create the following output:
As we can see three days between full moon and Easter Sunday was the most common occurrence and only once did it take longer than a week between full moon and Easter.
It was interesting to see in this challenge how the methods used to generate the desired output can be quite flexible.