Power BI DAX #4: CALENDAR, every sales/operations analyst friend

If there exists a book of ten commandments for Power BI, one of the commandments will be -- always have a date table.

A date table is a table that has just dates, and maybe any derivative of a date (month, quarter, year etc.)

Why do you need a date table?

Two easy/quick reasons:

1. You have many tables with date columns in each. It is better to have one table to include all the date periods you need and be related to all these other tables.
2. Even if you have just one table, as long as that table can have date gaps or 1900 for some records or 2090 for some records; you'll want to have your own better controlled date table rather than battle endlessly with wrong date entries in source data table. This is common when old data are migrated from a legacy tool.

So how do you create a DATE table easily in Power BI?

The answer is CALENDAR.

If I know how far back I want to go, then I would just specify that date as the start date and today as the end date.

Some other times, I would let CALENDAR use FIRSTDATE to get start date and LASTDATE to get end date.

Other times, I would specify a specific date and specify a specific end date (not TODAY, could be a future date).

For this illustration, I would use a specific start date and TODAY as the end date. Power BI will always increment the dates everyday -- that is the magic of putting TODAY as end date.

Calendar = CALENDAR(DATE(2016,01,01),TODAY())

Notice I used DATE to put in the specific date.

You can watch the YouTube tutorial: https://youtu.be/oodi9Vz0eeQ

Enjoy!

And to join our training class, visit https://www.urbizedge.com/powerbi