In this post, you will learn How to create an auto-generated calendar Table using the Power BI DAX CALENDERAUTO function based on a date field in your Model. Also, we will fix the known error “CALENDARAUTO function can not find a base column of DateTime type in the model.” that you may face when using Power BI DAX CALENDERAUTO.

What is DAX in Power BI?

Data Analysis Expressions (DAX) is a formula expression language explicitly designed for the management of data models, the DAX formulas include functions and operators that can be used to create formulas and expressions in Analysis Services, Power BI, and Power Pivot in Excel.

DAX in Power BI includes a library of over 200 functions, operators, that are used by report designers to perform data analysis and calculations.

Power BI DAX CALENDERAUTO

There are two DAX functions that make the process of creating a calendar table very simple and straightforward the CALENDER function and the CALENDERAUTO function.

What is the CALENDERAUTO function?

Power BI CALENDERAUTO function is one of the date and time functions in DAX that are similar to date and time functions in Excel which returns a table with a single column that contains a contiguous set of dates.

Syntax

CALENDARAUTO([fiscal_year_end_month])  

Any DAX expression that returns an integer from 1 to 12

Power BI DAX CALENDERAUTO Tips

1. The range of dates calculated automatically based on data in the model.
2. The fiscal_year_end_month is an optional parameter,it will use the calendar year instead and starts in January and ends in December
3. An error is returned if the model does not contain any datetime values.

Read more at CALENDARAUTO function can not find a base column of DateTime type in the model.

4. The start and end time would be based on the minimum and the maximum date value in your data model.
5. The CALENDARAUTO function ignores all calculated tables and calculated columns.
6. The earliest date the model is taken as the MinDate.
7. The latest date in the model is taken as the MaxDate.

Power BI DAX CALENDERAUTO Example

CALENDARAUTO()

The date will be generated based on the min and the max dates present in the data model ignoring any calculated tables and calculated columns.

SO CALENDARAUTO function generates the dates starting from the 3 January 2021 fiscal year associated with Min Date is 2021 to 3 April 2022 as fiscal year associated with Max Date is 2022. Then the result will be a Table with dates from 1/1/2021 to 12/31/2022 with 730 rows.

If you provide a fiscal_year_end_month value 3 to the CALENDARAUTO function.

CALENDARAUTO(3)

The result will be a table of dates from 4/1/2020 to 3/31/2023, and you can see now this time the date range is started from 4/1/2020.

And the Max Date range is 3/31/2023.

Solving: CALENDARAUTO function can not find a base column of DateTime type in the model.

As we earlier mentioned, the Power BI DAX CALENDARAUTO() is a predefined function that is used to auto-generate calendar dates range based on the date field in the current model.

So to get the Power BI DAX CALENDERAUTO, you must ensure that the current model has a table with a date field. Otherwise, you will get the CALENDARAUTO function can not find a base column of DateTime type in the model.

In this case, your current model doesn’t have a Table with a Date field, you can create a new table with a date field using EnterDate table like the following:

1. At the “Home” tab, click on “Enter Data” to create a new table.

2. The “Create Table” should be opened now to add your lookup values.
3. Add a column called “Date” to hold a date value
4. Finally, set the Table name, and click Load to load the newly created table.

5. Now, create a new table based on CALENDARAUTO() function.
6. Great, the new calendar date table has been created based on the provided date range in your date newly table that you have created.

Check also my answer at CALENDARAUTO function can not find a base column of DateTime type in the model.

Conclusion

In conclusion, we have explored the Power BI DAX CALENDERAUTO by discussing the following:

• What’s the Power BI DAX CALENDERAUTO?
• How the Power BI DAX CALENDERAUTO works?
• How to fix the “CALENDARAUTO function can not find a base column of DateTime type in the model” error that you may face when using Power BI DAX CALENDERAUTO.

Download

You can download the CALENDERAUTO DAX function examples file at Power BI DAX CALENDERAUTO, and don’t forget to follow Power BI Repo to get more PIBX files samples with complex DAX formulas.

See Also

• How to use Power BI DAX Bitwise Operations?
• Power BI concatenate two columns with space
• How to show Zero values in Power BI Chart?
• Power BI Custom Sort order in Bar Chart

Join us

• Subscribe to Power Platform Geeks.
• Register to Saudi Arabia Power Platform User Group.

Need Help

• Have a related question? Please ask it at deBUG.to Community.