Fiscal Year Week over Week

[dogt1225]

I am looking to show the fiscal week over week change. 
The image below show the current data I am getting with my calculations, but there is an error at the start of a new Fiscal Year. The area I've highlighted in yellow should be a 26 (Last week's Submitted Count), but its coming up as zero since week 52 > week 1. Any ideas on what I should change in the "Submitted Previous Week" calculation?

Submitted Date (Count) = COUNT(Req[Submitted_Date])

Submitted Previous Week = 
     Calculate(Req[Submitted Date (Count)],
        FILTER(ALL('Calendar'),
        'Calendar'[Fiscal Week Num] = SELECTEDVALUE('Calendar'[Fiscal Week Num]) - 1 &&
            'Calendar'[Fiscal Year] = SELECTEDVALUE('Calendar'[Fiscal Year])
        )
     )

[manikumar34]

@dogt1225 

Try something like this

Submitted Previous Week = 
     Calculate(Req[Submitted Date (Count)],
        FILTER(ALL('Calendar'),
        if(WEEKNUM(MAX('Calendar'[Date]))=1,
         'Calendar'[Fiscal Week Num] = Calculate(MAX('Calendar'[Fiscal Week Num]),ALL(Calendar)) &&
            'Calendar'[Fiscal Year] = VALUE(MAX('Calendar'[Fiscal Year])-1),
        'Calendar'[Fiscal Week Num] = VALUE(WEEKNUM(('Calendar'[Date)) - 1 )&&
            'Calendar'[Fiscal Year] = VALUE(MAX('Calendar'[Fiscal Year]))
        )
     )
)

[amitchandak]

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

[dogt1225]

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

[Anonymous]

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