topic Re: DATEDIFF only returning same value in DAX Commands and Tips

DATEDIFF only returning same value

ThomasSan — Mon, 28 Mar 2022 08:49:05 GMT

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!

Re: DATEDIFF only returning same value

johnt75 — Mon, 28 Mar 2022 08:58:55 GMT

Change DATEDIFF to DATEADD, everything else seems fine

Re: DATEDIFF only returning same value

ThomasSan — Mon, 28 Mar 2022 09:10:19 GMT

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?

Re: DATEDIFF only returning same value

johnt75 — Mon, 28 Mar 2022 09:19:19 GMT

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

Re: DATEDIFF only returning same value

tamerj1 — Mon, 28 Mar 2022 18:15:16 GMT

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 )

Re: DATEDIFF only returning same value

ThomasSan — Tue, 29 Mar 2022 08:22:14 GMT

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.

Re: DATEDIFF only returning same value

ThomasSan — Tue, 29 Mar 2022 08:32:21 GMT

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.