Power BI DAX Calendar Function [Create Calendar Table using DAX]

In this Power BI tutorial, I have explained how to use the Power BI DAX calendar function and also, and we will see how to create calendar table using DAX. I will also show you various examples of calendar function in Power BI.

I recently worked on a Power Bi report, where I got a requirement to use the calendar function and filter the table data values based on the need. To achieve it, I have used the Power BI Dax Calendar function.

  1. What is the Power BI DAX calendar function?
  2. How to display calendar Table using Power BI DAX
  3. How to use the date function in Power BI DAX
  4. How can we check the time function in Power BI DAX?
  5. Power BI DAX calendar month
  6. Power BI DAX formula for the month name
  7. How to calculate the last calendar month using Power BI DAX
  8. How to use the Power BI DAX Weekday function
  9. Calculate the day of the year using Power Bi Dax
  10. Power bi Dax days in the month
  11. How to check Power bi Dax last day of the month
  12. How to find the calendar week number using Power BI DAX
  13. How to calculate last calendar week’s data using Power BI DAX
  14. Power bi Dax dates in the period
  15. How to use the Calendar Auto function in Power BI

Power BI DAX calendar function

Now, let us first understand what is the calendar function in Power BI.

The Power Bi calendar function returns a table with a single column added named Date that contains a contiguous set of dates. We can also set the range of dates from the specified start date to the specified end date.

Let us see how to use the Power bi Dax calendar function in Power bi. We will see an example that will return dates specified in the calendar function.

Power Bi Syntax for Calendar function:

CALENDAR(<start_date>, <end_date>)
  • Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded select the New Table option under the Home tab from the ribbon.
  • In the New table formula section, enters the below formula and select the check icon.
Calendar Table = CALENDAR(DATE(2022, 1, 1),DATE(2022, 1, 5))

Where,

  1. Calendar Table = New Table name
  2. Date = Function name
  • The screenshot below shows that the new table has been added with a single column called date and displays the set of date ranges passed in the formula.
Power BI DAX calendar function
Power BI DAX calendar function

This is how to use the Power bi Dax calendar function in Power bi.

Read Power BI Report Export to PDF

Power BI DAX calendar Table

Here we will create a calendar table in Power BI using DAX.

In this example, we will display the calendar table starting from today’s date till the end date as per the requirement.

  • Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded select the New Table option under the Home tab from the ribbon.
  • In the New table formula section, enters the below formula and select the check icon., Where the Startdate argument is passed as today function so that it will calculate the calendar table accordingly.
Calendar Table = CALENDAR(TODAY(),DATE(2023, 5, 31))

Where,

  1. Calendar Table = New Table name
  2. Today & Date = Function name
  • In the below screenshot, you can see that the new table has been added with a single column called date and displays the date value from today ( ie, Today’s date is 23/11/2022) till the date ranges passed in the formula.
Power Bi Dax generate calendar
Power BI DAX calendar Table

This is how to generate a calendar table using power bi Dax.

Check out, Power BI DAX Count

Power BI DAX date function

Let us see how we can use the Power bi Dax Date function with an example in Power bi.

The date function returns the table-specified date in DateTime format.

Power Bi Syntax for Date function:

DATE(<year>, <month>, <day>)
  • Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded, select the new measure option from the ribbon under the Home tab and apply the below-mentioned formula.
Fixed Date = DATE(2022,11,5)

Where Fixed Date = New Measure name

  • Now in the report section, select the Table visual from the visualization and then drag and drop the column fields and the created Date measure value in it.
  • By default, it will display the date in the date-time format.
  • We can also change the date time format to only the date format from the ribbon.
  • The screenshot below represents the fixed date value in the table visual as excepted.
Power BI DAX date function
Power BI DAX date function

This is how we can use the Power bi Dax Date function in Power bi.

See also  How to Sort by Multiple Columns in Power BI?

Read Embed Power BI report in SharePoint Online

Power BI DAX time function

Let us see how we can use the Power BI Dax time function in Power Bi with an example.

The time function converts hours, minutes, and seconds passed as numbers to time in DateTime format.

Power Bi Syntax for Time Function:

TIME(hour, minute, second)
  • Hour – Hour represents a number from 0 to 23.
  • Minute- Hour represents a number from 0 to 59.
  • Second- Hour represents a number from 0 to 59.
  • Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded, select the new measure option from the ribbon under the Home tab and apply the below mentioned formula.
Time function = TIME(3,0,0)

Where,

  1. Time function = New Measure name
  • Now from the visualization, select the Table visual and drag and drop car names, car models, and price field values and the created Time function measure value.
  • The screenshot below represents the time value in the DateTime Format as expected.
Power BI DAX time function
Power BI DAX time function

This is how to use the Power BI Dax time function in Power Bi.

Power BI DAX calendar month

Here we will see how to display the month number using the Power Bi Dax Calendar Month function in Power BI,

The Month function returns a number from 1 to 12 from 1 (January) to 12 (December).

Power Bi Syntax for Month Function:

MONTH(<datetime>)
  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar.
Month Value = MONTH('Calendar Table'[Date])

Where,

  1. Month Value = New Column name
  2. Calendar Table = Table Name
  3. Date = Existing column name
  • In the below screenshot, you can see that the new column displays the Month value based on the date column presented in the Power bi Calendar table.
Power BI DAX calendar month
Power BI DAX calendar month

This is how to display the month number using the Power Bi Dax Calendar Month function in Power BI.

Read Power BI report using People Picker Field

Power BI DAX formula for the month name

Let us see how we can extract the month name using the Dax formula in Power BI.

We use the Power BI Dax Format function to extract the month name from the table. In this example, I’ll extract the month name for the released date column presented in the vehicles table data.

  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar.
Month Name = FORMAT(Cars[Released Date],"MMMM")

Where,

  1. Month Name = New Column name
  2. Cars = Table Name
  3. Released Date = Existing column name

In the below screenshot, you can see that the new column extracts only the month name from the released date column.

Power BI DAX formula for the month name
Power BI DAX formula for the month name

This is the Formula to extract the month name using the Power Bi Dax in Power BI.

Power BI DAX last calendar month

Let us see how to calculate the last calendar month’s data using the Dax function in Power BI,

In this example, Initially, we will find the current month’s number and then we will calculate the last month’s data for the car price value.

  • Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded select the New Column option under the Home tab from the ribbon.
  • In the New column formula section, enter the below formula to find the current month value. (Ie. November (month number will be 11 )).
thismonth = MONTH(today())

Where,

  • this month= New Column name

In the same way, calculate the previous month’s value based on the date column presented in the table, for that select the New Column option and apply the below formula:

Previous Month = if (Cars[Released Date].[MonthNo]= Cars[thismonth]-1,Cars[thismonth]-1,Cars[Released Date].[MonthNo])

Where,

  1. Previous Month = New column name
  2. Cars = Table Name
  3. Released Date = existing column name
Power BI DAX last calendar month
Power BI DAX last calendar month
  • Where Current month is (November) so from the released date there is three car’s fall on the last calendar month.
  • So now we will calculate the car price value for the last month, for that apply the below-mentioned formula:
last month data = var amount =CALCULATE(SUM(Cars[Price]),Cars[Previous Month]=Cars[thismonth]-1) return amount

Where,

  • Last month’s data = New Column name
  • amount = Variable name
  • Cars = Table Name
  • Price, Calendar Week, and this week = Existing column names
  • In the below screenshot, you can see that the new column has been added and displayed the car price values only for the last calendar month.
Power BI DAX last calendar month example
Power BI DAX last calendar month example

This is how to calculate the last calendar month’s data using the Dax function in Power BI.

See also  Power BI Group by | Power BI Count Group by

Read Export Power BI reports to PDF

Power BI DAX Weekday function

Let us see how to use the Power BI Dax Weekday function in Power Bi with an example.

The Weekday function returns a number from 1 to 7 specifying the day of the week of a date. By default, the day ranges from starts from 1 (Sunday) to 7 (Saturday).

Power Bi Syntax for Weekday Function:

WEEKDAY(<date>, <return_type>)
  • If we select the return type as 1 then the week begins on Sunday (1) and ends on Saturday (7).
  • If we select the return type as  2, then the week begins on Monday (1) and ends on Sunday (7).
  • If we select the return type as 3, the week begins on Monday (0) and ends on Sunday (6).
  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar.
Weekday Column = WEEKDAY(Cars[Released Date],1)

Where,

  1. Weekday Column = New Column name
  2. Cars = Table Name
  3. Released Date = Existing column name
  • In the below screenshot, you can see that the new column displays the weekday value based on the released date column.
Power BI DAX Weekday function
Power BI DAX Weekday function

This is how to use the Power BI Dax Weekday function in Power Bi.

Power Bi Dax day of the year

Let us see how we can calculate the day of the year using the Power Bi Dax function in Power Bi.

Using the Power BI Dax DateDiff function we will calculate the day of the year which returns the number between the two dates.

  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar.
Day Of Year = DATEDIFF(STARTOFYEAR(Cars[Released Date]),Cars[Released Date],DAY)+1

Where,

  1. Day Of Year = New Column name
  2. Cars = Table Name
  3. Released Date = Existing column name
  • In the below screenshot, you can see that the new column displays the day-of-year number value based on the released date column.
Power Bi Dax day of the year
Power Bi Dax day of the year

This is how to calculate the day of the year using the Power Bi Dax function in Power Bi.

Read Export Power BI Reports to Excel 

Power bi Dax days in month

Here we will see how to calculate the days that are presented in the month in Power BI,

This example will calculate and show how many days are presented in a month. In this example, we will calculate and show you the count of days for the current month using the Today function,

  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar.
DaysinMonth = DAY( 
    IF(
        MONTH(TODAY()) = 12,
        DATE(YEAR(TODAY()) + 1,1,1),
        DATE(YEAR(TODAY()),  MONTH(TODAY()) + 1, 1)
    ) - 1
)

Where,

  1. Day in Month = New Column name
  2. Today = Function name

In the below screenshot, you can see that the new column displays the count of days in a month.

Power bi Dax days in the month
Power bi Dax days in the month

This is how to calculate the days that are presented in the month in Power BI.

Power bi Dax last day of the month

Here we will see how we can calculate the last day of the month using Power BI Dax in Power BI

Power BI EOMonth returns the last day of the month as the date in the date time format.

Power Bi Syntax for EOMonth Function:

EOMONTH(<start_date>, <months>)

In this example, we will use the End of the month function to calculate the last day of the month in Power BI,

  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar, From today’s date (28/11/2022) it will calculate the integer as a month.
LastdayofMonth = EOMONTH(TODAY(),1)

Where,

  1. LastdayofMonth = New Column name
  2. Today = Function name
  • In the below screenshot, you can see that the new column displays the last day of the month passed as the month parameter in the EOMonth function in Power BI.
power bi dax last day of month
power bi Dax last day of the month

This is how to calculate the last day of the month using Power BI Dax in Power BI.

Read Get Current Month Sales Report using Power BI Measure

Power BI DAX calendar week number

Let us see how we can use the Power BI Dax WeekNum function in Power Bi.

  • The WeekNum function returns the week number for the given date according to the return_type value.
  • The week number will be displayed based on the year, where January 1 is the first week of the year and is numbered week 1.

Power Bi Syntax for WeekNum Function:

WEEKNUM(<date>[, <return_type>])
  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar.
WeekNumber = WEEKNUM(Cars[Released Date])

Where,

  1. WeekNumber = New Column name
  2. Cars = Table Name
  3. Released Date = Existing column name
See also  What if parameter Power BI Date

In the below screenshot, you can see that the new column displays the week num value based on the released date column.

Power BI DAX calendar week number
Power BI DAX calendar week number

This is how to use the Power BI Dax WeekNum function in Power Bi.

Power BI DAX last calendar week

Let us see how we can calculate the last calendar week using the Dax function in Power BI,

In this example, Initially, we will find the current week’s number and then we will calculate the last week’s car price value.

  • Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded select the New Column option under the Home tab from the ribbon.
  • In the New column formula section, enter the below formula to find the calendar week number for the Released date column presented in the data table.
Calendar Week = WEEKNUM ( Cars[Released Date] , 1)

Where,

  1. Calendar Week = New Column name
  2. Cars = Table Name
  3. Released Date = Existing Column name
Power BI DAX last calendar week
Power BI DAX last calendar week
  • Now select the New Column option and apply the below formula to find the current week number,
thisweek = WEEKNUM(today())

Where,

  1. this Week = New Column name
Example of Power BI DAX last calendar week
Example of Power BI DAX last calendar week
  • Where Today’s date is ( 24/11/2022), so from the released date there are two car’s falls on the last calendar week.
  • So now we will calculate the car price value for the last week, for that apply the below-mentioned formula:
Last Week data  = var amount =CALCULATE(SUM(Cars[Price]),Cars[Calendar Week]=Cars[thisweek]-1) return amount

Where,

  • Last Week’s data = New Column name
  • Cars = Table Name
  • Price, Calendar Week, and this week = Existing column names
  • In the below screenshot, you can see that the new column has been added and displayed the car price values only for the last calendar week.
Power BI DAX last calendar week example
Power BI DAX last calendar week example

This is how to calculate the last calendar week using the Dax function in Power Bi.

Read Power BI DAX Filter Table

Power bi Dax dates in the period

Let us see how we can use the Date in Period function in Power Bi Dax in Power BI.

Power Bi DatesInPeriod function Returns a table that contains a column of dates based on the intervals,

Power Bi Syntax for DATESINPERIOD Function:

DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) 
  • Load the data into the Power Bi desktop, and select the New column option under the Home tab from the ribbon.
  • Apply the below-mentioned formula in the column section formula bar.
DatesInPeriod = DATESINPERIOD('Date'[Date],DATE(2022,11,28),1,DAY)

Where,

  1. DatesInPeriod = New Column name
  • In the below screenshot, you can see that the new column displays the return of the one-day date value as expected.
power bi dax dates in period
power bi Dax dates in the period

This is how to use the Date in Period function in Power Bi to calculate the date values.

Power BI DAX Calendar Auto

Here we will calculate and display the calendar table using the Calendar Auto function in Power BI,

In this example, we will display the calendar auto table. The Calendar auto function automatically scans the loaded data into the Power Bi desktop and finds the Date column and chooses the minimum and maximum date value and generates the Calendar table.

In this example, you can see below that the Minimum year from the Released date column is 2021, so it generates the calendar table from the year 2021.

Power BI DAX Calendar Auto example
Power BI DAX Calendar Auto example

Power Bi Syntax for Calendar Auto Function:

CALENDARAUTO([fiscal_year_end_month])
  • Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded select the New Table option under the Home tab from the ribbon.
  • In the New table formula section, enters the below formula and select the check icon.
CalendarAuto Table = CALENDARAUTO()

Where,

  1. Calendar Auto Table = New Table name
  2. CALENDARAUTO() = Function name
  • In the below screenshot, you can see that the new table has been added with a single column called date and displays the date value from the minimum year 2021 automatically.
Power BI DAX Calendar Auto
Power BI DAX Calendar Auto

This is how to calculate and display the calendar table using the Calendar Auto function in Power BI.

This is how to create or generate a calendar using the Power BI DAX in Power BI, Also examined the below-mentioned topics in this Power Bi tutorial.

  • Power BI DAX calendar function
  • Power BI DAX calendar Table
  • Power BI DAX calendar month
  • Power BI DAX Weekday function
  • Power BI DAX calendar week number
  • Power Bi Dax day of the year
  • Power BI DAX calendar Auto
  • Power BI DAX formula for the month name
  • Power BI DAX time function
  • Power BI DAX now function
  • Power BI DAX last calendar week
  • Power BI DAX last calendar month
  • Power bi Dax days in the month
  • Power bi Dax dates in the period

You may like the following Power BI tutorials:

>