I have a date table which I use as a slicer. I want date1 to get the minimum date from that range. I then use that date to calculate the number of new user from KPI_summary
table andIi want this to be a fixed unchanging value which is returned as newUsers
.
However when I plot this with dates the values change. am I missing something? pls see codes below
NewUsers = var date1 = MIN('Date Table'[Date]) -- get the first date from the slicer var newUsers = calculate(sum(KPI_SUMMARY[NEW_USERS]), KPI_SUMMARY[JOIN_DATE] = date1) --get the new users from the first date return newUsers -- I want to always return the newUsers from the first date of the slicer. it shouldnt change
Try something like this:
NewUsers =
-- get the first date from the slicer
var _date1 =
CALCULATE(
MIN('Date Table'[Date]),
ALLSELECTED('Date Table')
)
var _newUsers =
calculate(
sum(KPI_SUMMARY[NEW_USERS]),
KPI_SUMMARY[JOIN_DATE] = _date1
) --get the new users from the first date
return
_newUsers -- i want to always return the newUsers from the first date of the slicer. it shouldnt change
I used ALLSELECTED() function inside CALCULATE() function to change _date1 variable calculation context. Also, as you can see, I would advice you to name your variables with "_" prefix. It's much easier to recognize them then.