Dax dateadd in measure doesn't work as expected

[ed7]

I have 2 measures: 

Cases as of today = Sum('!Data'[Cases])

Cases as 20 days ago = Calculate(Sum('!Data'[Cases]), DATEADD('Calendar'[Date].[Date],-20,DAY))

//My Data table has # of cases for each calendar date. Calendar table has a relation to the Data table.

In the visual I filter to last date, my expectation to see the current # of cases and # that was 20 days ago, BUT .. both numbers are the same.  

Any idea about what I'm doing wrong? 

Thank you so much.

PS: it was my stupid typo here: 'Calendar'[Date].[Date]. Thank you for helping me to catch it,

[nandukrishnavs · Accepted solution]

@ed7 

Cases as of today = SUM('!Data'[Cases])

Cases as 20 days ago = 
var _lastdate= max(Calendar[Dates])
var _total=CALCULATE(SUM('!Data'[Cases]),DATESINPERIOD(Calendar[Dates],_lastdate,-20,DAY))
return _total

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous · Accepted solution]

Please do yourself a favour and create a proper Calendar. Do not ever rely on the automatic date hierarchy if you want to create correct formulas.

Please note that such syntax:

'Calendar'[Date].[Date]

is a sign you're not doing it right.

Best
D

[ed7]

@Anonymous Could you please elaborate on creating proper Calendar, please?  I fixed my typo in formula, tried to use your code. Still doesn't work

[Anonymous]

Hi there.

Marco will explain to you all about Calendars:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D

[nandukrishnavs · Accepted solution]

@ed7 

Cases as of today = SUM('!Data'[Cases])

Cases as 20 days ago = 
var _lastdate= max(Calendar[Dates])
var _total=CALCULATE(SUM('!Data'[Cases]),DATESINPERIOD(Calendar[Dates],_lastdate,-20,DAY))
return _total

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂