Forum Discussion

seefadeeb's avatar
seefadeeb
Frequent Visitor
2 years ago
Solved

Help with DATEADD function

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....
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
anmolmalviya05
Super User
2 years ago

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's avatar
seefadeeb
Frequent Visitor
2 years ago

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