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 that I am using is here:

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

 

Can anyone please help me correcting my DAX so that it returns the figure for international sales from three months ago? I would require it in order to measure the growth of international sales within the last three months.

 

Thank you in advance!

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi ThomasSan 

    As johnt75  statated it is allways better to have a Standard Date Table and build a proper data model with the required relationships. I would also recommend to follow the good practice. However, If wish to continue without a date table and you don't want to add additional columns then you may try the following hopping that the EOMONTH function is not a time intelligence function that requires a standard date table:

    Test =
VAR FirstDateInFilter =
    MIN ( 'Table'[Date] )
VAR Date3MonthsAgo =
    EOMONTH ( LastDateInFilter, -3 )
RETURN
    CALCULATE (
        SUM ( 'Table'[International Sales] ),
        'Table'[Date] >= Date3MonthsAgo,
        'Table'[Date] < FirstDateInFilter
    )

     

    • ThomasSan's avatar
      ThomasSan
      Helper IV
      4 years ago

      Hi tamerj1 ,

       

      thank you for your reply. I was acutally not aware that a separate date table was necessary for time intelligence functions to work properly. Thank you for pointing that out, I just learnt a tiny new bit of valuable PBI information 🙂

      And you are right, I should follow best practice and always use a separate date table from now on.

  • 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