Last Value of previuos week

[Anonymous]

Hi,

i have a table (Ratings) with multiple values (5 Star Ratings) per day and they're cumulated, like this:

Date	Weekday	Weeknum	5 Star Ratings
28.02	SUN	9	1.000
28.02.	SUN	9	1.100
01.03	MON	10	1.150
01.03	MON	10	1.160
02.03	TUE	10	1.200
03.03	WED	10	1.300
03.03	WED	10	1.356
...
07.03	SUN	10	1.500

GOAL: I want a measure that is showing me per week the last value of the previous week. So i can calculate averages etc. with differences etc.

So like: 

	Current	Previous
Week 9	1.100	...
Wek 10	1.500	1.100

Current week is easy with SUM and MAX Date. 

For previous week I tried like this:

Prev.Week = 

CALCULATE(SUM(Ratings[5Star]),FILTER(ALL(Ratings),Ratings[Weeknum]=MAX(Ratings[Weeknum])-1&&Ratings[Date]=MAX(Ratings[Date])))

It doesn't show me anything.

Thx for your help.

Micha

[themistoklis · Accepted solution]

@Anonymous 

You can try the following formula:

Prev.Week = 
CALCULATE(SUM(Ratings[5Star]),FILTER(ALL(Ratings),Ratings[Weeknum]=(MAX(Ratings[Weeknum])-1) && Ratings[Date]=(Ratings[Date]+ 7-WEEKDAY(Ratings[Date],2)) ) )

[amitchandak]

@Anonymous , Check My WOW blog, it can help, using rank and Week/Date table

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

@amitchandak thx. Weekrank helped a lot thx.

I have the sum of the previous week so far:

CALCULATE(SUM(Ratings_iOS[1Star]),FILTER(ALL(Date_global),Date_global[Week Rank]=MAX(Date_global[Week Rank])-1))

How do i combine it with MAX, to get only the latest value of the previous week?

[themistoklis]

@Anonymous 

If you want the value from last day of previous week try adding this to your formula:

&& Ratings[Date]=(Ratings[Date]+ 7-WEEKDAY(Ratings[Date],2))