Recycling Date Tables

[learning_dax]

Hi all,

I am trying to figure out the easiest, most future-proof way to reuse a calendar table in Power BI (and eventually in Tableau). I have a Date table that I have built in Power BI desktop that works great but it is not easily reusable. When I create a new report I have to create all 20 columns by scratch with the DAX code per each column in the new report (for example Month Offset = DATEDIFF(TODAY(, [Date], MONTH) etc for all columns. 

I then tried creating a Calendar table in Power Query but quickly realized that the Day/Month offsets will not be accurate unless refreshed everyday. Thus, if a day passes and I do not refresh that day my day offset = 0 (which should be today) will be yesterday. That is probably not the most effective way to refresh the Calendar table every day. It seems like my only option will be to create these tables in Tableau/Power BI within the models (so that it calculates TODAY as the day viewers are seeing the report without refresh) but how can I write a recyclable code to generate all the columns and their formules in one query? For instance, Date (CalendarAUTO), Start of Year (STARTOFYEAR(Date, "5/31"), Fiscal Year (YEAR([Start of Year] +1), etc. 

I'm also open to anyone's suggestions as to how I can solve this issue of not being able to reuse Date tables espeiclaly with offset columns. I love using dataflows/datamarts & would love to have a template of some sort. Thank you!

[collinq · Accepted solution]

Hi @learning_dax ,

The standard methodology is to make one Date Dimension that you reuse in all of your reports.  You can do this by creating a template with just the date dimension in it, or you could do another common method and put that query editor into notepad and then copy/paste it into the advanced editor.  This method means you never have to re-do or add any steps in each subsequent reprot.

You are correct though that you can't get "today" as today's date without a refresh of the data.  All the formula's inside the dimension, just like all of the data in a query, are only as current as the last refresh.

However, the common method for that is to publish the report and have the Service automatically update it for you.

There are lots and lots of Date Dimension examples on the internet (some DAX and some M).  Personally, I prefer the M method so that other people who don't know DAX can more easily configure the dimension in the future.  That said, since you asked for an example, here is one that you can start with:  https://epmstrategy.com/why-create-a-date-table-in-power-bi/