This week and same week last year

[PowerBIPilgrim]

Hi All, 

I've tried a couple of methods to get this to work, but I can't quite make it gel. I have a Table that has the following columns:

Amount, Payment Date, New Subscription and Category.

I also have a Calendar dimension that is joined on Payment Date, in the Calendar Dimension and in that Table I have a WeekNum column (along with other date formats). I have 3 graphs:

1: Showing the Dollar Amount of New Subscriptions, paid for in the last 7 days, by category

2: Showing the Dollar amount of Renewal Subscription (e.g. where the new Subscription flag isn't set), paid for in the last 7 days, by category

3: Showing Total Dollar Amount, paid for in the last 7 days

Currently, for each graph I just have a relative date filter - however I've been asked to add to each of these a secondary value for the same time last year. So this means I'll have to remove the relative date filter and probably tweak my current measures and create a new measure for the same period last year - however I've not had any success doing this,

[amitchandak · Accepted solution]

@PowerBIPilgrim , refer my blog on week vs week . you need additional column in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

And measure like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

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