DATEADD function

[Anonymous]

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. 

[amitchandak · Accepted solution]

@Anonymous , 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

[Anonymous]

First Thanks for your answer .

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

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

[amitchandak]

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

[Anonymous]

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.