Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
6 years ago
Solved

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

Need Help

7 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    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's avatar
      DebbieE
      Community Champion
      6 years ago

      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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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's avatar
      DebbieE
      Community Champion
      6 years ago

      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's avatar
        v-lili6-msft
        Community Support
        6 years ago

        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