Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ThomasSan
Helper III
Helper III

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:

ThomasSan_0-1648457129264.png

 

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!

1 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

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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.

johnt75
Super User
Super User

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:

ThomasSan_0-1648458285233.png

 

Here is the underlying table if that helps:

ThomasSan_1-1648458477147.png

 

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

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

ThomasSan_1-1648538529408.png


My current DAX command for Test:

 

Test = 
CALCULATE(
    sum(SalesTable[International Sales]),
    DATEADD(
        'Date'[Date],
        -3,
        MONTH
    )
)

 

And here is my data model:

ThomasSan_0-1648537392145.png

 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors