Calculate and dateadd issue

[James_Barrett]

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

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(SUM('Users/New Users'[New Users]),DATESBETWEEN('CalendarTable'[Date],[Last Week Start],[Last Week Start]))

Hope this helps.

[v-yulgu-msft]

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

[Greg_Deckler]

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...

[James_Barrett]

OK, thanks Greg. I'll have a look.

James