Forum Discussion

anandav's avatar
anandav
Skilled Sharer
8 years ago
Solved

DateAdd function in column not working

Hi,   I was trying answer another thread and hit a problem on DateAdd function. I was trying to calculate previous month sales amount for a given sales date. I have a Date table and created 1:many...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi anandav,

     

    This is a question of context, if you remove the previous month from your table view it will give you the calculation you need.

     

    Be aware that the measure are very sensitive in terms of context and if you don't place a certain column in your measure, when you add that measure with that column the value will be off.

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
8 years ago

Hi anandav,

 

Why are you trying to create a calculated column with previous month to calculate the total sales of previous month?

 

The best way is to have a calculated measure using the DATEADD or similar.

 

Your measure should look something like this:

 

Prev Month Sale = 
CALCULATE(SUM(Sales[Amount]); DATEADD(Sales[Date]; -1; MONTH))

Beware that using the DAX formula on columns is different from measures, since the context is given in different way, that's why the Prev. Month sales 1 is working and the second one is not since you are taking context from your DAX formula and the calculation is incorrect.

 

Regards,

MFelix

anandav's avatar
anandav
Skilled Sharer
8 years ago

Hi MFelix,

That is exactly what I am strugling with - I have used DateAdd in Prev Month Sale2 and it is just giving me blank. Whether I do it as a column or measure it is blank.