Forum Discussion
Calculate and dateadd issue
Hi guys
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.
Any ideas?
Thanks,
James
4 Replies
- Greg_Deckler
Community Champion
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/339586
OK, thanks Greg. I'll have a look.
James
v-yulgu-msftMicrosoft Employee
Hi James_Barrett,
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))Best regards,
Yuliana Gu
- Ashish_Mathur
Super User
Hi,
Try this measure
=CALCULATE(SUM('Users/New Users'[New Users]),DATESBETWEEN('CalendarTable'[Date],[Last Week Start],[Last Week Start]))
Hope this helps.
