Nico Jacobs on 02 Dec 2016 03:40:26
In Excel we can join a date dimension with the fact table on a surrogate key (int) and the time intelligence functions in DAX (DatesYTD etc) work fine if we first mark our date table using the "mark as date table" option.
However in Power BI Desktop there is no such option, hence we have to join our date table on a date column with our fact table or we cannot use time intelligence.
So please add the option to mark a table as a date table to Power BI Desktop.
Administrator on 07 Dec 2020 17:36:15
Completed as of February 2018
- Comments (4)
RE: enable "mark as date table" in Power BI Desktop
Is possible since PBI Update Feb 2018
RE: enable "mark as date table" in Power BI Desktop
You should set the state as completed because it has been implemented in February 2018 version.
RE: enable "mark as date table" in Power BI Desktop
Hi Karel,
A clarification must be made. DAX time intelligence functions that use the standard calendar may work properly in Power BI. However, DAX time patterns used for other custom calendars do no work without the "mark as date table". See post https://community.powerbi.com/t5/Desktop/How-to-mark-Date-table/td-p/3202/page/2 . greggyb's comment is true. I've experienced it.
RE: enable "mark as date table" in Power BI Desktop
'Hi Nico,
not sure if I get your comment right. I did the following test in PBI Desktop:
- I've created a fact table and a date dimension
- relation based on integer key (e.g. 20160708)
- my date dimension contains a field of datetype 'Date'
>> I've succesfully added calculated measures with DAX functions DATESYTD and SAMEPERIODLASTYEAR, referring to my [Date dimension].[Date] field