Forum Discussion

dogt1225's avatar
dogt1225
Helper III
5 years ago

Fiscal Year Week over Week

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])
        )
     )

 

 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

    • dogt1225's avatar
      dogt1225
      Helper III
      5 years ago

      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. 

  • manikumar34's avatar
    manikumar34
    Solution Sage
    5 years ago

    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]))
        )
     )
)