Forum Discussion
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 between Date and Sales table.
Original Data:
| Compnay | Date | Amount | Prev Month |
| A | 01/01/2018 | 10 | |
| B | 01/01/2018 | 20 | |
| A | 01/02/2018 | 30 | 01/01/2018 |
| B | 01/02/2018 | 40 | 01/01/2018 |
| A | 01/03/2018 | 50 | 01/02/2018 |
| B | 01/03/2018 | 60 | 01/02/2018 |
Intended Results:
| Compnay | Date | Amount | Prev Month | |
| A | 01/01/2018 | 10 | ||
| B | 01/01/2018 | 20 | ||
| A | 01/02/2018 | 30 | 01/01/2018 | 10 |
| B | 01/02/2018 | 40 | 01/01/2018 | 20 |
| A | 01/03/2018 | 50 | 01/02/2018 | 30 |
| B | 01/03/2018 | 60 | 01/02/2018 | 40 |
I created a column as below which works:
Prev Month Sales1 =
CALCULATE(
MIN(Sheet1[Amount]),
FILTER(ALL(Sheet1),
Sheet1[Date] = EARLIER(Sheet1[Prev Month]) && Sheet1[Compnay] = EARLIER(Sheet1[Compnay])
)
)
But when I tried to use DateAdd function it gives me blank.
Prev Month Sales2 =
CALCULATE(MIN(Sheet1[Amount]), DATEADD(Sheet1[Date], -1, MONTH))
Why is the DateAdd not working in either as a column or a measure?
When I use DateAdd just in a column I get the date in date/time format.
Prev Month From DateAdd =
CALCULATE(DATEADD(Sheet1[Date], -1, MONTH))
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
8 Replies
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
