cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors