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

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.