DATEADD function with CALCULATE not working as expected

[OscarHernandez]

Hi all,

I am seeing that my LastMonth metric is not returning the expected results depending on what Dates I'm filtering.

My data model has a Calendar table with dates that range from 01/01/2022 to 05/03/2024 (DD/MM/YYYY) which is related to a Fact table. Then I created the following measures:

Measure= DISTINCTCOUNT(MyTable[MyColumn])

Measure_LM = CALCULATE([Measure],DATEADD('Calendar'[Date],-1,MONTH))

Then I have a slicer that lest me select which Calendar'[Date] I want to filter.

Measure_LM works perfectly when my slicer is selecting anything but the maximum Date from my Calendar table. However, when my slicer is set to 01/03/2024-05/03/2024, the value I get from Measure_LM is not the DISTINCTCOUNT from 01/02/2024-05/02/2024 but I'm getting the values of the whole month 01/02/2024-29/02/2024. 

Why does DATEADD take "full month" when my slicer is set to the maximum available 'Calendar'[Date]? 

Thanks in advance!

[amitchandak]

@OscarHernandez , Make sure the slicer is on the column from the date table . I tried same on Table and card visual. I getting value for those days

[OscarHernandez]

Hi @amitchandak,

Thanks for your response. I checked my slicer and it is already using the date column from the calendar table.

Looking at you screenshot, I see that your slicer is not set to the maximun posible date which is the problem I'm facing right now. I am attaching 2 screenshot so you can better see what my problem is. Plese note that, in this example, the maximum date in the calendar table is 03/03/2024 (DD/MM/YYYY)

In the first screenshot, metrics for LastMonth (_LM) and LastYear (_LY) are working as expected, aggregating data from 01/02/2024-02/02/2024 and 01/03/2023-02/03/2023 respectively. 

In the second screenshot, you can see that the result shown in card visual or total row in table visual is not working properly. It is aggregating data from 01/02/2024-29/02/2024 in _LM metric and 01/03/2023-31/03/2023 in _LY metric. 

Do you know why is it working like that? It seems like it is treating the maximum available date as "end of month" so it converts into last day of the month in LastMonth/LastYear metrics.

Thanks for your time.