cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rfrikha1992
Helper I
Helper I

DATEADD function

Greeting lovely members.

 

i used the function Dateadd and it seems like it's glitching  (im not sure tho)

Last year sales = CALCULATE(Fact_Sales[Sales Value],DATEADD(Dim_Date[Date],-1,YEAR)) 


=> normally here it will show the sales of last year , but when i put my results in a table it shows the sales of the next year which is the opposite (when i change -1 with 1 it shows the correct results ) 
 
Any idea ? Thanks in advance. 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Rfrikha1992 , If this is a measure

Like

Last year sales = CALCULATE(Sum(Fact_Sales[Sales Value]),DATEADD(Dim_Date[Date],-1,YEAR)) 

 

or Sales Value is a measure. then your formula

 

if you need a new column , please share data

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Rfrikha1992 , If this is a measure

Like

Last year sales = CALCULATE(Sum(Fact_Sales[Sales Value]),DATEADD(Dim_Date[Date],-1,YEAR)) 

 

or Sales Value is a measure. then your formula

 

if you need a new column , please share data

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

First Thanks for your answer .

Rfrikha1992_0-1633341516186.png

that's my output . as you see LY Sales measure show sales of last year  so all good  . 

Rfrikha1992_1-1633341725040.png

my question here why it doesnt work when i put -1 in the interval and works when i put 1 ? 

 

 

 

@Rfrikha1992 , it should work. Just make a sure the year is from dim_Date, also it is marked as date table

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Yes it is . 

i used an M function to build my DimTable . and the type of the column Dim_date[Date] is a Date.

The function DateAdd accept only Date column anyway.

 

Last year sales = CALCULATE((Fact_Sales[Sales Value]),DATEADD(Dim_Date[Date],1,YEAR)) => Shows the sales of last year.
 
Last year sales = CALCULATE((Fact_Sales[Sales Value]),DATEADD(Dim_Date[Date],-1,YEAR)) => 
Show the sales of next year .
 
is it better to create the Date table with Dax rather than M language ? thanks again for your answers.
 
 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.