Dynamic Calendar Table in Power Bi Power Query Editor

Consider a scenario where a client has asked to display the sales quantity for each day from the given sales data, which also includes a sales date column. Upon preparing a simple bar chart, you notice that some dates are missing because no sales occurred on those dates.

However, the client wants to see all dates, including those with zero sales quantity. Therefore, we cannot rely solely on the dates in the sales date column. We need to create a separate date table that includes all dates from a given start date to today.

To achieve this, follow the steps below:

Step 1: Open Power Query Editor, create a blank query, and insert the following M code formula to set the start date.

Here, #date is a function in Power Query Editor in Power BI that generates a date literal based on the provided year, month, and day parameters.

Step 2: After the date literal (single date) is populated, click on the "fx" button in the formula bar to enter a new formula. This formula will create a list of dates between the start date and the current date.

Let's break down the formula (the M code):

List.Dates: This is a Power Query formula (M Code Language) that generates a list of dates between the given dates. It takes three parameters:

a. Start date: The date from which the list of dates should be generated.

b. Count: The number of dates to generate.

c. Step: Describes the duration between each date.

Source: This variable holds the start date created in Step 1.

DateTime.LocalNow(): Returns the current date and time.

Number.From(): Converts the date and time to an integer value, representing the number of days since a specific start date (Power BI generally uses December 31, 1899).

Number.From(DateTime.LocalNow()) - Number.From(Source): Calculates the number of days between the current date and the start date.

#duration(1,0,0,0): This represents a duration value of 1 day (1 day, 0 hours, 0 minutes, 0 seconds).

Step 3: The above formulas create a list of date values between two dates. However, we cannot use this list directly in Power BI unless we convert these dates to a table format. To do this, click on the "Convert to Table" button in the list transformations tab and set the delimiter (we select "None" since there are no delimiters in our list).

We have now created a dynamic calendar table using the Power Query Editor.

Significant Points to Remember:

• After converting the list to a table, we can add additional date values to the table using the date functions under the "Add Column" tab.
• Since we used DateTime.LocalNow(), refreshing the report after a few days will update the calendar with dates from the start date to the report refresh date.
• Be mindful that M-Code is case-sensitive.