Past 12 Weeks Over Year End/Start

[moesteez]

Hi guys,

Ive got a simple calculated column in DAX that marks the past 12 complete weeks:

Is Past 12 Weeks = IF(AND('Calendar'[Week Number] > WEEKNUM(TODAY()) - 13, 'Calendar'[Is Less Than Curent Week] = 1), 1, 0)

This obviously only works properly once the first 12 weeks of the year have gone by. Does anyone know how I could write this so that when the new year starts, my past 12 weeks are 1, 53, 52, 51... etc?

[amitchandak · Accepted solution]

@moesteez , you do b creating a week rank on Year week YYYYWW or weel start date

example : Refer to my blog for more details  - Make sure week is in date or separate table

Week Rank = RANKX(all('Date'),'Date'[Week Year],,ASC,Dense)
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 12 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

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...
https://www.youtube.com/watch?v=pnAesWxYgJ8