topic Re: Help with DATEADD function in DAX Commands and Tips

Help with DATEADD function

seefadeeb — Wed, 26 Jun 2024 10:50:25 GMT

I wrote the DATEADD dax function as below

Previous Month = CALCULATE(SUM(Sales[Sales Amount]),DATEADD('Sales'[Order Date],-1,MONTH))

It is not giving the desired result as in the below screenshot. It is returning blank rows in months

@Fowmy @amitchandak @Ahmedx @Ritaf1983

Re: Help with DATEADD function

anmolmalviya05 — Wed, 26 Jun 2024 11:55:49 GMT

Hi seefadeeb, Hope you are doing good!

Your DAX formula looks correct, You have to check few things as it's not working fine.

1) Firstly please check the data type of the order date column, it should be Date type

2) If you are using a date table and using the year & month column in visual from date table, then please verify that there should be an active relationship between order date and date column.

I hope this will help you!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: Help with DATEADD function

seefadeeb — Thu, 27 Jun 2024 07:42:28 GMT

The order date is date type and there is not date table. How can i send the PBIX file to you to verify?

Re: Help with DATEADD function

Anonymous — Thu, 27 Jun 2024 08:51:24 GMT

Thanks for the reply from anmolmalviya05 , please allow me to provide another insight:

Hi seefadeeb ,

Check your data model, especially the field types and inter-table relationships for the date table. In my test, using the same formula, it shows up fine.

Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

PM Sales = CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, MONTH ) )

Best Regards,
Adamk Kong

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

Re: Help with DATEADD function

seefadeeb — Thu, 27 Jun 2024 11:25:54 GMT

Thanks for the reply Anonymous

In the PBIX file you attached, if i change the formula as below without using date table I am getting blanks

PM Sales = CALCULATE ( [Sales Amount], DATEADD ( Sales[Order Date], -1, MONTH ) )

Can you explain this behaviour?

Re: Help with DATEADD function

Anonymous — Fri, 28 Jun 2024 08:38:39 GMT

Hi seefadeeb ,

Use the Date table:

DATEADD ( 'Date'[Date], -1, MONTH )

When you use above formula, Power BI can correctly understand dates and their relationships because Date is a specialized date table that is connected to the Sales table by a relationship.
The Date table contains a contiguous and complete set of dates, which helps the DATEADD function work correctly and return the expected results.

Use Order Date in the Sales table:

DATEADD ( Sales[Order Date], -1, MONTH )

When you use above formula, the function may not work as expected because Sales[Order Date] does not provide a continuous set of dates. It only contains dates with sales transactions.
DATEADD relies on a continuous date range to calculate the offset date. Since Sales[Order Date] may have gaps and not cover every date, this causes the DATEADD function to return null values.

Best Regards,
Adamk Kong

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