You are currently viewing How to use Power BI DAX CALENDERAUTO?

How to use Power BI DAX CALENDERAUTO?

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.

  1. The start and end time would be based on the minimum and the maximum date value in your data model.
  2. The CALENDARAUTO function ignores all calculated tables and calculated columns.
  3. The earliest date the model is taken as the MinDate.
  4. 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.

auto1 | Power Platform Geeks

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.

Power BI DAX CALENDERAUTO

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.

auto4 | Power Platform Geeks

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

auto8 | Power Platform Geeks

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.

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.
Create a Lookup Table in Power BI
  1. The “Create Table” should be opened now to add your lookup values.
  2. Add a column called “Date” to hold a date value
  3. Finally, set the Table name, and click Load to load the newly created table.
create a new table in power bi
  1. Now, create a new table based on CALENDARAUTO() function.
  2. Great, the new calendar date table has been created based on the provided date range in your date newly table that you have created.
Power BI DAX CALENDERAUTO
Power BI DAX CALENDERAUTO

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
Join us
Need Help
  • Have a related question? Please ask it at deBUG.to Community.

Mohamed El-Qassas

I'm Microsoft MVP, SharePoint StackExchange Moderator, Microsoft QnA Moderator, C# Corner MVP, Microsoft TechNet Wiki Judge, and Senior Technical Consultant with +12 years of experience in SharePoint, Project Server and Power Platform.