Forum Discussion
Fiscal Year Week over Week
dogt1225 , You can create Week Rank (FYYYWW) or on the week start date and use that.
New column in date table for FY
Start Year = STARTOFYEAR('Date'[Date],"3/31") //FY end ar 3/31 - change it
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
FY Year = YEAR('Date'[Start Year]) // use end year
FY Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[FY Year]=EARLIER([FY Year])),'Date'[Start of Week]),[Date],DAY),7)+1
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[FY Year Week],,ASC,Dense) //YYYYWW format
Try measures 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
https://www.youtube.com/watch?v=pnAesWxYgJ8
amitchandak Thank you for the response. I am getting the following error when I use RANKX.
"A single value for column 'FY (num) + FW(num)' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"
FY(num) + FW(num) calculation results in fields YYYYMM.
My fiscal calendar is a bit more complex... a fiscal year starts on the fourth Saturday of each calendar year.
Hi dogt1225,
Please provide a sample pbix file with some dummy data to test, it should help us clarify your data structures and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng