cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DebbieE New Contributor
New Contributor

Dax DATEADD question why adding .date changes things

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))
LastYearsSalesLC.JPG
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
 
 

LastYearsSalesLCError.JPG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dax DATEADD question why adding .date changes things

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Super User I
Super User I

Re: Dax DATEADD question why adding .date changes things

@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

 

 

Community Support Team
Community Support Team

Re: Dax DATEADD question why adding .date changes things

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))

 

1.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DebbieE New Contributor
New Contributor

Re: Dax DATEADD question why adding .date changes things

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. 

DebbieE New Contributor
New Contributor

Re: Dax DATEADD question why adding .date changes things

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

Community Support Team
Community Support Team

Re: Dax DATEADD question why adding .date changes things

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.

3.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DebbieE New Contributor
New Contributor

Re: Dax DATEADD question why adding .date changes things

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.

Community Support Team
Community Support Team

Re: Dax DATEADD question why adding .date changes things

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors