Help with DATEADD function

[seefadeeb]

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 

[v-kongfanf-msft]

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.

[seefadeeb]

Thanks for the reply @v-kongfanf-msft

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?

[v-kongfanf-msft · Accepted solution]

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.

[anmolmalviya05]

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

[seefadeeb]

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