Forum Discussion

ALeef's avatar
ALeef
Resolver II
10 years ago

Possible Tip/Trick: Dynamic Date Dimension Table

I'm going to share the dynamic date dimension table I built, hopefully it is useful to someone!  I named it DateTable for this tutorial, and my fact table FactTable.   The table is built using the ...
Tips and Tricks
lanceengland's avatar
lanceengland
Regular Visitor
10 years ago

Nice tip. As usual, there are several ways to do the same thing with DAX. A function similar to CALENDAR is CALENDARAUTO which scans all the date columns and returns a range of dates from the earliest date to the latest date. It is very handy.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    lanceengland However, the CALENDARAUTO isn't dynamic right? I mean it scans for all the dates from earliest to the latest. But if the data source is updated, the CALENDARAUTO doesn't update automatically. Someone can correct me if i'm wrong. 

     

    Edit: I just realized that CALENDARAUTO is in fact dynamic. Tried it with an Excel data source. Removed some dates and the table named DateTable (created with calendarauto) updated its date range after a refresh.

    • lanceengland's avatar
      lanceengland
      Regular Visitor
      10 years ago

      Yes. Dynamic is this sense is at process time i.e. refreshing the data model from the data source. On that note, while I haven't verified it, I'm guessing CALENDARAUTO would not work in Direct Query mode. That said, if you were in Direct Query mode, you would handle the dynamic date range at the data source.