Dax DATEADD question why adding .date changes things

[DebbieE]

I have a DAX query to look at last years sales using Adventureworks

Last Years Internet Sales = CALCULATE(FactInternetSales[# Sales Amount], DATEADD(DimDate[FullDateAlternateKey],-12,MONTH))

This is great, but when Im writing the DaX Query it always wants me to do this

Last Years Internet Sales = CALCULATE(FactInternetSales[# Sales Amount], DATEADD(DimDate[FullDateAlternateKey].[Date],-12,MONTH))

Note the .date which changes to this

Last year and Sales are now the same. Im trying to get my hear round why adding the .Date changes the Query so much. Has anyone got an easy to understand answer to this one?

Thnaks in advence

[v-lili6-msft]

hi @DebbieE 

.[Date] only calculate in date hierarchy of one viusal.

here is a simple sample will give you a better understand.

Last Years Internet Sales = CALCULATE(SUM('Table'[Qty]), DATEADD('Date'[Date],-12,MONTH))

Last Years Internet Sales 2 = CALCULATE(SUM('Table'[Qty]), DATEADD('Date'[Date].[Date],-12,MONTH))

Regards,

Lin

[DebbieE]

This concept is just so hard to understand. You say that .date means that it only works on the date rather than the hierarchy but my visual does use date rather than the hierarchy so I still just dont understand how this all works. 

[v-lili6-msft]

hi @DebbieE 

".[Date]" only works on hierarchy date rather than date, this just means when you use Date hierarchy for your date filed, it will work.

Regards,

Lin

[DebbieE]

Ahhhh so If Im using the date hierarchy then .Date should be fine. However because Im using just date I dont need the .date. Ill have to test that out. Thankyou.

[v-lili6-msft · Accepted solution]

hi @DebbieE 

Yes, usually, we don't need to use .[Date] in dax, it only work in date hierarchy row context.

https://docs.microsoft.com/en-us/power-bi/desktop-auto-date-time

Regards,

Lin

[VasTg]

@DebbieE 

Quite Interesting..

I Just have a theory..

If I have a table with one row and one date column  with value 1/1/2019, and when I create a table visual with date, I only see 1 row - 1/1/2019.

But when I change the attribute date to show hierarchy, I see all the dates in 2019. 

Also, test this out by creating two new tables as below.

New Table = DATEADD(TABLE1[DATE].[DATE],-11,MONTH)
New Table = DATEADD(TABLE1[DATE],-11,MONTH)

The first table(With hierarchy) returns only January dates(-11 actually removes all the preceding 11 months on 12/31/2019; when you specify -12, table is null)

The second table(without hierarchy) actually brings in previous year if you have data in table, otherwise null.

Basically the DATEADD function behaves differently when we specify hierarchy.

So, looking at your example, when you use hierarchy, the dateadd returns null and it has no effect on CALCULATE statement.

Disable the date hierarchy in Desktop - Options->data load ->Time intelligence->Auto Date/Time

-vasanth

[DebbieE]

I will have a work through this. Just having a look at this answers Im just as stuck as I was before. Sometimes Dax is incredibly difficult to get your her around