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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors