Forum Discussion

nrj1806's avatar
nrj1806
Frequent Visitor
4 years ago

DATEADD function - results vary

Hello,   To provide a bit more context, i am trying to count all the records, on a rolling basis, 1 month before any agreed delivery date. So any record, that has a delivery date in the upcoming 1 ...
Need Help
nrj1806's avatar
nrj1806
Frequent Visitor
4 years ago

1 month to Agreed delivery date = CALCULATE(  
   COUNTROWS('TP_ACTIVITIES'),  
   DATEADD('TP_ACTIVITIES'[AgreedDeliveryDate], -1, MONTH)  
)
 
 
1 month to Agreed delivery date =
 CALCULATE(
            COUNTROWS('TP_ACTIVITIES') ,
            FILTER('TP_ACTIVITIES', DATEADD('TP_ACTIVITIES'[AgreedDeliveryDate], -1, MONTH
                )
            )
)

 

There is a difference in the result, could you help me understand why?

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi nrj1806 ,

    In my test ,the two measure return the same value ,could you pls share your sample data and output result,remember to remove confidential data.

     

     

     

    And I add a few blogs and videos that you could reference to learn more about the function of DATEADD.

    Using DATEADD() and other Date Tips for Filtering Data in Power BI 

    The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI 

    DATEADD function (DAX) - DAX DATEADD function In Power BI Desktop 

    DAX Fridays #16: DATEADD 

     

    Best Regards

    Lucien

    • nrj1806's avatar
      nrj1806
      Frequent Visitor
      4 years ago

      I have added a picture of the data. Both the cards should show the same numbers considering the dates in the column.

       

      I have tried three formulas to pick the dates from last month, but they dont seem to work. 

       

      1 month to Agreed delivery date = CALCULATE(  
         COUNTROWS('TP_ACTIVITIES'),  
         DATEADD('TP_ACTIVITIES'[AgreedDeliveryDate], -1, MONTH)  
      )
       
       
      From last 1 MONTH =
       CALCULATE(
                  COUNTROWS('TP_ACTIVITIES') ,
                  FILTER('TP_ACTIVITIES', DATEADD('TP_ACTIVITIES'[AgreedDeliveryDate], -1, MONTH
                      )
                  ))
       
      Next 1 month = calculate(COUNTROWS('TP_ACTIVITIES')
      ,FILTER(ALL('TP_ACTIVITIES'[AgreedDeliveryDate]),
      not(ISBLANK('TP_ACTIVITIES'[AgreedDeliveryDate])) &&
      DATEADD('TP_ACTIVITIES'[AgreedDeliveryDate], -1, MONTH)))