Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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))  ...
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
amitchandak
Super User
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.