Forum Discussion

ThomasSan's avatar
ThomasSan
Helper IV
4 years ago
Solved

DATEDIFF only returning same value

Hi people,   I am having the issue that my DATEDIFF returns the same value again (rather than the corresponding value from three months ago). The problem looks as follows:   The dax command...
DATEDIFF
Time Intelligence
  • johnt75's avatar
    johnt75
    4 years ago

    To work properly the time intelligence functions need a date table which contains all the dates for any given year. Create a date table and link it to your fact table, and then use the 'Date'[Date] column instead of the 'Table'[Date] column

johnt75's avatar
johnt75
Super User
4 years ago

Change DATEDIFF to DATEADD, everything else seems fine

ThomasSan's avatar
ThomasSan
Helper IV
4 years ago

Hi johnt75 ,

 

thank you for your reply. When inserting DATEADD to my command, it returns the following table:

 

Here is the underlying table if that helps:

 

Do you happen to have an idea what is going wrong here?

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    To work properly the time intelligence functions need a date table which contains all the dates for any given year. Create a date table and link it to your fact table, and then use the 'Date'[Date] column instead of the 'Table'[Date] column

    • ThomasSan's avatar
      ThomasSan
      Helper IV
      4 years ago

      johnt75 

      I was not aware that a separate date table was necessary for time intelligence function to properly work. Thank you for that information!

       

      Unfortunately, the problem persists:


      My current DAX command for Test:

       

      Test = 
CALCULATE(
    sum(SalesTable[International Sales]),
    DATEADD(
        'Date'[Date],
        -3,
        MONTH
    )
)

       

      And here is my data model:

       

      I created my date table with the following dax formula:

       

      Date = 
VAR MinYear = 2021
VAR MaxYear = 2022
RETURN
ADDCOLUMNS (
calendar( date(MinYear,1,1) ,Date(MaxYear,12,31)
),
"Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)

       

       

      Do you see where I might have made a mistake?

       

      Edit:
      I found the error. I also needed to adjust the date fields in my matrix. So, rather than using year and month from the date in my sales table, I needed to insert the year and month from my date table. It works now.