Calendar in DAX, Power BI

As a Power BI developer, more than often you'll likely need to track trends over time. It goes without saying that at some point you will be engaging with a calendar.

In DAX, you can easily create a calendar table with all the features of a calendar you need, may it be Quarters in a year or month numbers.

We will explore creating a calendar table :

The first step is specifying the range of dates you want your calendar to contain and you can contain that in variables. You will need to have a calendar that includes the dates present in your calendar

dimCalendar =
VAR Mindate = MIN(Expenditure[Date]
VAR Maxdate = TODAY()
RETURN
ADDCOLUMNS(
CALENDAR(Mindate, Maxdate),
"YEAR" YEAR([Date]),
"MONTH" MONTH([Date]),
"DAY" DAY([Date]),
"MONTHNAME" FORMAT([Date], "MMM"),
"QUARTER", "Q" & QUARTER([Date])
)

The above is a simple calendar that:

1. Defines the dates you need in your calendar table as variables
2. You need to ADDCOLUMNS to the calendar, and begin by having it said that the calenda will contain the mindate and the maxdate. 3.Specify the columns as needed. Whatever we have in quotation, is the name of the column then returns respective value.
3. The format in the Monthname ensure we return our months as 'Jan, Feb' simply as 3 letter words.
4. For the Quarter column, the additional Q ensures the name of quarters begin with the prefix Q. This way it adds much more sense.

There you go, try out this simple calendar and use it in your reports.

Anything and Everythind Data!