In this post we explore the CALENDAR DAX Function. This particular function unlike stuff we used before doesn’t really perform a calculation instead it creates a date table we can use within other formula. If you are familiar with Business Intelligence systems you will know that almost all of them have some kind of date or calendar table. Many measures used in BI systems are based on time intelligence. An example of this would be percentage change in sales Year on year. These calculations are impossible to do without some table providing details of the dates.
Unlike SUM, CALCULATE etc. CALENDAR can’t be used like a measure i.e. dragged and dropped. It is embedded into other formulas.
The below example shows how to use CALENDAR to create a table containing dates between 2020-01-01 and 2020-03-01
With the table created we can begin to use the table just like any other table within the database. The table still needs to have relationships established between itself and the other tables. This table only exists within PowerBI and therefore can be modified by the users. If you do not want users to modify the dates consider using a database table instead.
There is no SQL equivalent for this function.