Error with DateAdd

[Dimitris_Kats]

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! 

[Dimitris_Kats]

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!!

[Anonymous · Accepted solution]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

@Dimitris_Kats 

Sorry just noticed your reply. Would you pleas share a screenshot of you date table?

[Dimitris_Kats]

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

[amitchandak]

@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-bd52912a5b...

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.

[Dimitris_Kats]

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 🙂

[tamerj1]

@Dimitris_Kats 

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