DATEADD function - results vary

[nrj1806]

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 month must be counted.

I am a bit confused about the way DATEADD function works. The two measures below give me different answers, only difference being one is in DAYS and one is in MONTH

1 month to Agreed delivery date = CALCULATE(  

   COUNTROWS('TP_ACTIVITIES'),  

   DATEADD('TP_ACTIVITIES'[AgreedDeliveryDate], -1, MONTH)  

)

1 month to Agreed delivery date 2 = CALCULATE(  

   COUNTROWS('TP_ACTIVITIES'),  

   DATEADD('TP_ACTIVITIES'[AgreedDeliveryDate], -30, DAY)  

)

Can someone help me understand why there would be a difference?

Thank you

[nrj1806]

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]

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]

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

[nrj1806]

I see where i could be potentially going wrong now. I shouldnt be using 30 for month at all since some months have 31 days or 28. 

[Avantika-Thakur]

Hi @nrj1806 ,

Does the month that you are using in the 1st DAX expression (of Month) the one which has 30 days in it?