Power Pivot Principles: The A to Z of DAX Functions – CALENDAR

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at the CALENDAR function.

The CALENDAR function returns a table of all dates from a specified start date to a specified end date.  While we can use this function to create a new separate Calendar table in Power BI, Power Pivot (i.e. the Excel version) does not allow you to create a new table the same way that Power BI does.  However, the CALENDAR DAX function is still available in Power Pivot because it returns a table of values, and that resulting table can be nested in other DAX formulae to create calculations and measures.

Its syntax is as follows:

It has two arguments:

• StartDate: the start date or DAX expression that returns a datetime value
• EndDate: the end date or DAX expression that returns a datetime value.

It should be noted that:

• there will be an error if StartDate is greater than EndDate
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Examples

• The formula below returns a table with all the dates in November 2021:

• To make it dynamic with your own dataset, the following example returns a table covering all dates between the start and end dates of the Transactions table:

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.