Forum Discussion
Error with DateAdd
Dear members Hello
I am calculating the Current Month Sales amount:
CM Sales = CALCULATE(SUM(Sales[Sales]))
Also the Previous Month Sales
PM Sales = CALCULATE(SUM(Sales[Sales]), DATEADD(Time[Month], -1, Month))
And last i am checking if there are any changes on sales amount month by month
Sales Dif = IF(ISBLANK([CM Sales]), BLANK(),IF(ISBLANK([PM Sales]),blank(), IF([CM Sales] = [PM Sales], BLANK(), [CM Sales] - [PM Sales]))))
This measures works well and it gives me the desired results. If there are new products that doesn't have any sales yet i get them as blank rows.
When i am trying though to add a Filter to remove tha BLANK rows (Sales Dif is not BLANK) i get an error that DATEADD expects a contiguous selection.
I am using a calendar table and the problem is when I select on my slicer months of previous year.
Is there any way to fix this?
Thank you in advance!
Hi Dimitris_Kats ,
Try to sumx your original measure to get correct result in total.
PM Sales with correct Total = SUMX(VALUES('Time'[Month]),[PM Sales])Sales Dif with correct Total = SUMX(VALUES('Time'[Month]),[Sales Dif])Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Dimitris_Kats , You should always use a date table for that
PM Sales = CALCULATE(SUM(Sales[Sales]), DATEADD(Date[Date], -1, Month))
refer
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.Hello amitchandak and thank you very much for your reply.
I am using a date table for this measure . I use the month column from the date table wich is formatted as date.
This is not the problem 🙂The explanation provided by amitchandak is correct. DATEADD deals with a column of unique date values. The month column even if formatted as date is not unique. Use the date column with DATEADD or YearMonth Number column and subtract 1
Thank you very much tamerj1 for your reply.
Unfortunatelly when i use the calendar date I don't get results at all.
I tried to use instead of the DATEADD the PREVIOUSMONTH but i don't get correct results and i don't get results by month.
I don't know what is wrong
So, I used PREVIOUSMONTH with the month column of my calendar table and the PM Sales (PM Sales = CALCULATE(SUM(Sales[Sales]), PREVIOUSMONTH (Time[Month])) works well and i don't get an error when i am changing the time periode on the slicer.
The only problem now is that i get either wrong row total or no row total at all.
Do you know can i fix this?? I am sooooo close!!Sorry just noticed your reply. Would you pleas share a screenshot of you date table?
Hi Dimitris_Kats ,
Try to sumx your original measure to get correct result in total.
PM Sales with correct Total = SUMX(VALUES('Time'[Month]),[PM Sales])Sales Dif with correct Total = SUMX(VALUES('Time'[Month]),[Sales Dif])Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
