Forum Discussion

OscarHernandez's avatar
OscarHernandez
Frequent Visitor
2 years ago

DATEADD function with CALCULATE not working as expected

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...
Need Help
amitchandak's avatar
amitchandak
Super User
2 years ago

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

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.