Forum Discussion
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/
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.
I recommend DateStream from the Azure Data Marketplace, free.
https://datamarket.azure.com/dataset/boyanpenev/datestream
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.
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
