topic Re: Error with DateAdd in DAX Commands and Tips

Error with DateAdd

Dimitris_Kats — Tue, 19 Apr 2022 07:15:34 GMT

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!

Re: Error with DateAdd

amitchandak — Tue, 19 Apr 2022 07:49:17 GMT

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.

Re: Error with DateAdd

Dimitris_Kats — Tue, 19 Apr 2022 08:00:55 GMT

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 🙂

Re: Error with DateAdd

tamerj1 — Tue, 19 Apr 2022 09:15:10 GMT

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

Re: Error with DateAdd

Dimitris_Kats — Tue, 19 Apr 2022 09:58:20 GMT

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

Re: Error with DateAdd

Dimitris_Kats — Tue, 19 Apr 2022 11:34:33 GMT

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

Re: Error with DateAdd

tamerj1 — Tue, 19 Apr 2022 13:15:33 GMT

Dimitris_Kats

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

Re: Error with DateAdd

Anonymous — Fri, 22 Apr 2022 08:10:40 GMT

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.