New to Power BI.
I'm trying to figure out why my CALCULATE and DATEADD formula isn't working.
UsersLastWeek = CALCULATE(SUM('Users/New Users'[New Users]),dateadd(FIRSTNONBLANK('CalendarTable'[Week Start],[Week Start]),-7,DAY))
In the highlighted row I would like to have the previous week's value (42) shown in the UsersLastWeek column.
You need to use EARLIER most likely. See my article on it here, different problem but you can use the same technique. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Proud to be a Datanaut!
I tested with above formula, it returned expected result.
In your scenario, does 'CalendarTable' list unique continual dates?
Please refer to my steps to get the previous week value.
Generate a calendar table. Establish a one to many relationship from calendar table to users table.
CalendarTable = CALENDAR(MIN(Users[Week Start]),MAX(Users[Week Start]))
New a measure similar to yours.
UsersLastWeek = CALCULATE(SUM('Users'[New Users]),dateadd(FIRSTNONBLANK('CalendarTable'[Date],CalendarTable[Date]),-7,DAY))