How to mark Date table?

[teolachev]

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

[AlexGorev]

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.

[desertislesql]

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?

[desertislesql]

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

[AlexGorev]

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.

[MattAllington]

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?

[greggyb]

@MattAllington, the 'Mark As Date Table' function alters the behavior of filter arguments to CALCULATE() (all of the built in scalar Time Intelligence functions are syntactic sugar for CALCULATE( [measure], <table Time Intelligence function). Normally, when we define a filter argument in CALCULATE() it is rewritten as follows:

// DAX
CALCULATE(
    [BaseMeasure]
    ,DimTable[FilterField] = <literal>
)

// That is rewritten internally to the
// following format

CALCULATE(
    [BaseMeasure]
    ,FILTER(
        ALL( DimTable[FilterField] )
        ,DimTable[FilterField] = <literal>
    )
)

When we 'Mark as Date Table', these semantics are altered slightly. Any filter applied to the marked date table will first clear *ALL* filter context from the table, rather than just the filter field.

// DAX
// If our DimTable is a marked date table, we'll
// see an internal rewrite like this:
CALCULATE(
    [BaseMeasure]
    ,MarkedDateDim[FilterField] = <literal>
)

// This is converted to the following semantics
CALCULATE(
    [BaseMeasure]
    ,FILTER(
        ALL( MarkedDateDim )
        ,MarkedDateDim[FilterField] = <literal>
    )
)

This semantic shift is reflected in the built-in Time Intelligence functions.

The most dramatic difference is that marking a date table will allow you to use a field other than the date to join between fact and DimDate. It's very common, especially in established SQL DWs and Multidimensional OLAP to see the join key between fact and DimDate be defined on an integer field, with the date-type field being simply an attribute of that integer key.

This works fine with a marked date table, but if you don't 'Mark as Date Table', then an integer key will function incorrectly with the built-in time intelligence in DAX. We don't have marking functionality in PBI, so we *must* join on the date field between fact and DimDate for the built-in Time Intelligence functions to work.

Try it for yourself. Pull in a date dimension with an integer key and some fact with both [DateKey] and [Date] in it. Use some Time Intelligence functions. Then set the active relationship to [Date]. Everything's great. Then try it on [DateKey]. It doesn't work correctly.

What causes this behavior for an integer key? When the Time Intelligence function filters [Date], it clears context on [Date]. If we're joined on [DateKey], that context is not cleared. If we've joined on [Date], then there's no conflict in context between the join key and the filter field.

[MattAllington]

Thanks @greggyb.  Yes I was aware of the "remove time filter context" that comes from Mark as Date Table and the syntax sugar approach.  I was not aware of the "use any key to join the tables but then have a different date column for the time intelligence" difference between Excel and Power BI (although I figured there must be a difference given the lack of a "mark as date table" in Power BI).

I have always felt that the "mark as date table" is superfluous.  Every single inbuilt time intelligence function requires you to specifiy the Calendar[date] column in the measure.  So it seems to me that you need to tell Power Pivot 'twice' which is the date column.  Surely if you "mark as date table" then you shouldn't need to specify the date column in the time intelligence function.  I assume that no other date column from any other table will work, so that would not be an option.

[Anonymous]

As of 2017, is it supposed to mark as a Data table? Where is the menu option?

Regards

[MattAllington]

This is not required/possible in Power BI.  If you want to use a calendar table, you must use a date field and join on that.  Then it will work as designed.  In Power Pivot for Excel you can use a surrogate key.  In this case you must mark as date table.

[marcorusso]

You can use the workaround described in this article to mark a table as a Date table in Power BI:

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[marcorusso]

The Mark As Date Table feature has been implemented in February 2018 version.

[diverdown1964 · Accepted solution]

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/

[Greg_Deckler]

I recommend DateStream from the Azure Data Marketplace, free.

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

[Greg_Deckler]

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.

[Greg_Deckler]

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