Forum Discussion
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?
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/
16 Replies
I am not aware of anything special that you do to mark a date table in Power BI Desktop.
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.
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?
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! :)
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.
