Forum Discussion
DATEDIFF only returning same value
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
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
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.