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.

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.