Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Problem with CalendarAuto()

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 (th...
Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    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.

HotChilli's avatar
HotChilli
Community Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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).