Power Query function, which calculates the date of Easter for the year received as a parameter, and is able to determine other moving holidays related to Easter (Good Friday, Pentecost).
EasterDate(yearNumOrDateOrDateTime as any, optional holidayName as nullable any, optional julianCalendar as nullable any) as date
Possible parameters, their types and accepted values.
- yearNumOrDateOrDateTime - number, text, date, datetime
- holidayName (optional) - text, number - allowed values: Easter, EasterSunday, EasterMonday, GoodFriday, Pentecost, PentecostSunday, PentecostMonday or day modifier -2, 0, 1, 49, 50
- julianCalendar (optional) - logical
| yearNumOrDateOrDateTime | holidayName | julianCalendar | Result |
|---|---|---|---|
| 2024 | 2024-03-31 | ||
| 2024-01-01 | 2024-03-31 | ||
| 2024-12-31 12:34:56 | 2024-03-31 | ||
| "2024" | 2024-03-31 | ||
| "2024-01-01" | 2024-03-31 | ||
| "2024-12-31 12:34:56" | 2024-03-31 | ||
| 2024 | "Easter" | 2024-03-31 | |
| 2024 | "Good Friday" | 2024-03-29 | |
| 2024 | "EasterMonday" | 2024-04-01 | |
| 2024 | "Pentecost" | 2024-05-19 | |
| 2024 | "PentecostMonday" | 2024-05-20 | |
| 2024 | "-2" | 2024-03-29 | |
| 2024 | -2 | 2024-03-29 | |
| 2024 | false | 2024-03-31 | |
| 2024 | true | 2024-04-22 | |
| 2024 | "Easter" | true | 2024-04-22 |
| 2024 | "GoodFriday" | true | 2024-04-20 |
- Download the example or start a new Power BI project or open a blank Excel workbook.
- Open Power Query Editor
- PowerBI Open Transform data under the Home tab, Queries group.
- Excel Launch Power Query Editor under the Data tab, Get and Transform group, Get Data dropdown menu.
- PowerBI Open Transform data under the Home tab, Queries group.
- Add a new Blank Query
- PowerBI Click on the Blank Query under the Home tab, New Query group, New Source dropdown menu.
- PowerBI Click on the Blank Query under the Home tab, New Query group, New Source dropdown menu.
- Excel Click on the Blank Query under the Home tab, New Query group, New Source dropdown menu, Other Sources submenu.
- Open Advanced Editor under the Home tab, Query group.
- Copy and paste the EasterCalculatorFunc file contents
- Rename the function to whatever you want (Ex: EasterDateCalculator)
Press the right mouse button on the function name in the left panel and choose rename!
- Click on the function to invoke to itself
- Or add it to your table as a new column.
Click on the Invoke Custom Functino under the Add column tab, General group.