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. It is returning blank rows in months
  • 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.

5 Replies

  • 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.