The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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
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
)
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.
Change DATEDIFF to DATEADD, everything else seems fine
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?
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.
User | Count |
---|---|
20 | |
17 | |
15 | |
14 | |
12 |
User | Count |
---|---|
34 | |
34 | |
20 | |
19 | |
18 |