cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
thracian Regular Visitor
Regular Visitor

How to mark Date table?

How do we mark a Date table as such in Power BI Desktop so that DAX date formulas, such as TOTALYTD, work?

1 ACCEPTED SOLUTION

Accepted Solutions
diverdown1964 Regular Visitor
Regular Visitor

Re: How to mark Date table?

You can also use PowerQuery itself to create a date table. Matt Mason has a great post on this here - http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

 

 

15 REPLIES 15
Super User
Super User

Re: How to mark Date table?

I am not aware of anything special that you do to mark a date table in Power BI Desktop.


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

Proud to be a Datanaut!


Power BI Team AlexGorev
Power BI Team

Re: How to mark Date table?

We do not have Calculated Tables or Date Tables in the PBI Desktop yet... Proper date time support with ability to use DAX time functions is one of the key scenarios that we are working on. We should improve in this area in the next couple months.

 

Alex.

Re: How to mark Date table?

Does this mean that if we need to do Date Calculcations with DAX we should model in Power Pivot in order to be able to include them in our reports?

Super User
Super User

Re: How to mark Date table?

That's probably your best bet, Power Pivot has the ability to mark tables as date tables and has all of the time intelligence stuff in it.


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

Proud to be a Datanaut!


Power BI Team AlexGorev
Power BI Team

Re: How to mark Date table?

Ability to mark a table as DateTime is used in PowerPivot for UI optimizations only, it does not enable or disable any DAX Time Intelligence functions.

 

In order to use time time functions you need to have a Table that meets these requirements:

  - Contains at least one column of type Date.

  - Contains exactly one row per day for all the range of days.

  - No gaps in days for all supported range of dates.

  - Have a relationship with your Data Table (somethig like SalesDate).

  - ... I think that is all... hope not missing anything! Smiley Happy

 

Currently in the PBI Desktop you can create a table like this by either importing from Excel (or other data source) or you can create it in the Query View.

 

We are curently working on the Calculated Table feature that will allow you to create tables (including Date tables) in the Data View using DAX.

 

Hope this helps!

Alex.

Super User
Super User

Re: How to mark Date table?

I recommend DateStream from the Azure Data Marketplace, free.

https://datamarket.azure.com/dataset/boyanpenev/datestream

 

 


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

Proud to be a Datanaut!


Re: How to mark Date table?

If you are interested in how to create a marked date table in Power BI which can use all the DAX time functions, this post should help http://www.desertislesql.com/wordpress1/?p=821

diverdown1964 Regular Visitor
Regular Visitor

Re: How to mark Date table?

You can also use PowerQuery itself to create a date table. Matt Mason has a great post on this here - http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

 

 

Super User
Super User

Re: How to mark Date table?

Alex

 

the "mark as date table" feature in Excel Power Pivot has always bemused me. It seems to be superfluous given that every time you write a time intelligence function you must specify the datetable[date column] again. Can you explain how marking as date table is used for UI optimisations?

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.