DATEDIFF only returning same value

[ThomasSan]

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!

[tamerj1]

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]

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]

Change DATEDIFF to DATEADD, everything else seems fine

[ThomasSan]

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 · Accepted solution]

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]

@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.