Forum Discussion
This week and same week last year
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,
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-Last-Week/ba-p/1051123
1 Reply
- amitchandak
Super User
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-Last-Week/ba-p/1051123