Forum Discussion
Calculate Function with DATEADD and Filter not working
I have the code for current month, which works well for me. However if I check the DATEADD to -1, the current month row data doesn't show the previous month value in the table visual. Any idea why?
CurrM = CALCULATE(
SUM('CADUSD'[Balance]),
FILTER(
'CADUSD', 'CADUSD'[Data Type] <> "Plan"
),
DATEADD(
Calendar[Date], 0, MONTH
)
)
PrevM = CALCULATE(
SUM('CADUSD'[Balance]),
FILTER(
'CADUSD', 'CADUSD'[Data Type] <> "Plan"
),
DATEADD(
Calendar[Date], -1, MONTH
)
)
5 Replies
- ray_aramburo
Super User
You are filtering your data for February so in the filter context there's nothing to be shown for January but it appears since you included a Previous Month value as well. For Current Month you should remove the DateAdd portion of the measure, it is not needed.
if I remove the months filter, the CurrM column now has more than 1 values. I should expect CurrM to only show only the february number when i have DATEADD, 0, MONTH (see image attached)
- not sure why i did the same in my model and it wouldn't work. the result is the same as per my original screenshot.
- ray_aramburo
See my example attached on how to set up the measures, you can adjust as needed:
I used a similar structure of yours but changed my totals just to serve as an example.
