Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
learning_dax
Helper II
Helper II

Recycling Date Tables

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!

1 ACCEPTED SOLUTION
collinq
Super User
Super User

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/ 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




View solution in original post

1 REPLY 1
collinq
Super User
Super User

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/ 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors