cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
James_Barrett Frequent Visitor
Frequent Visitor

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

Example Power BI.png

 

 

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 4
Super User
Super User

Re: Calculate and dateadd issue

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


James_Barrett Frequent Visitor
Frequent Visitor

Re: Calculate and dateadd issue

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

 

 

James

Community Support Team
Community Support Team

Re: Calculate and dateadd issue

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]))

1.PNG

 

New a measure similar to yours.

UsersLastWeek = CALCULATE(SUM('Users'[New Users]),dateadd(FIRSTNONBLANK('CalendarTable'[Date],CalendarTable[Date]),-7,DAY))

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Calculate and dateadd issue

Hi,

 

Try this measure

 

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

 

Hope this helps.