Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
19.9k views
in Power BI by 26 30 35

I have tried to use CALENDARAUTO() to create a date table for the current year but I got this error 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


1 Answer

1 like 0 dislike
by 161 202 395
selected by
 
Best answer

Using CALENDARAUTO() in Power BI

First, you should be aware of the CALENDARAUTO() is a predefined function that is used to auto-generate calendar dates range based on a date field in the current model. So if the current model doesn't have a table with a date field you will get CALENDARAUTO function can not find a base column of DateTime type in the model.

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

If you don't have any table that holds a date field in your current model, you can easily create a new table with a date field like the following:

  • On the Home tab, click on the Enter Data button to create a new table.
    enter data in power bi
  • The new table dialog will be shown.
  • Rename the column header as you prefer, and provide a meaningful table name, then add the date values in the correct format.
  • Click load to load the new table.
    create a new table in power bi
  • Now, create a new table based on CALENDARAUTO() function.
  • Great, the new calendar date table has been created based on the provided date range in your date newly table that you have created
    create a new table in power bi

You can also create a date table range using CALENDAR DAX function as mentioned at How to use DAX Calendar function in Power BI?

How CALENDARAUTO() works in Power BI

The CALENDARAUTO() generates a full year date range based on the date field in the current model.

  • The start date would be based on the minimum date in the current model.
  • The end date would be based on the maximum date in the current model.

Example:

  • Consider you have a date field with the below values
    date range in power bi

  • So the generated calendar table will hold a date range for three years from 01/01/2018 to 31/12/2020 as shown below:
    3 years calendar date table

Read more at How to use Power BI DAX CALENDERAUTO?

If you don’t ask, the answer is always NO!
...