Problem with CalendarAuto()

[Anonymous]

I am following advice on how to look at changes in invoices in this thread and the solutions requires the use of the CalendarAuto() function. More specifically, it requires the use of a DateTable (that I am trying to figure out) but I'm getting an error (when trying to use the following code, stating "CALENDARAUTO function can not find a base column of DateTime type in the model". (First Screen shot)

As to the table with date-related info, I am getting "Month_Nbr" and "Year_Nbr" from the SQL database (there is no "date" field ironically) and then I concantenate that and convert that to a "Data Type = Date", the "Combined_Date" field (Second Screen shot).

I then also create a "New Date" field which is just a dupliate of the "Combined_Date" field, but made into a "Date/Time" type (third screen shot).

I'm not sure why I'm getting the error with CalendarAuto(). Any help would be appreciated!

[Anonymous]

@HotChilli , that was it. Apparently it didn't like the DAX and I was able to use the #date(year, month, day) in PQ. Thank you SO much for your help!

[HotChilli · Accepted solution]

Instead of constructing the date column in InvoiceData table via DAX, you would do it in Power Query.

Add a custom column similar to this :

#date(year, month, day)  - all numbers.

Make sure it has date type.

Calendar auto should be able to find and use this.

-----

If you want to go the DAX route, using CALENDAR function, an example is:

CALENDAR (
    DATE ( 2010, 1, 1 ),
    DATE ( 2017, 12, 31 )
)

you could hardcode the month and day and use MinYear and MaxYear variables which you already have.

[speedramps]

Dont use CALEANDERAUTO.

Click here to watch free videos instead. Including a free calendar download. 

It is a really good video course

Please click thumbs up and accept as solution button.  Thanks ! 😀

[HotChilli]

CalendarAuto can't find any relevant dates. I think you are expecting the calculated column to be the date but calendarauto doesn't like dax calculated columns.

You should probably create the datecolumn in power query (or use the CALENDAR dax function and create the dates to use as parameters)

[Anonymous]

@HotChilli that would make sense (not liking the created date column).

I'm rather new and was hoping you might give an example of what you mean by creating the date column in PQ please. (Thank you for your feedback).