Fiscal Calendar that Starts on a Monday, has 52 weeks and runs from July to June

[ClaireBear]

Hello, 

I need to build a Fiscal Calendar that Starts on a Monday, has 52 weeks and runs from July to June. 

I have a current date table with the date, week, month and year, and have even managed to get the fiscal month in as July being month 1 and June month 12. The issue im having is with adding week 1 to 52 and for week 1 to start on a Monday. 

Is anyone able to assist?

i have added the normal week number : 

Week Number = WEEKNUM('Date'[Date],2)

and used this dax for the fiscal week number (but it does not start on a Monday and is over 52 weeks):

Fiscal Weeknum =

VAR __StartingWeekNum = WEEKNUM(DATE(YEAR([Date]),7,1))

VAR __EndingWeekNum = WEEKNUM(DATE(YEAR([Date]),12,31)) - __StartingWeekNum + 1

RETURN

SWITCH(TRUE(),

'Date'[Week Number] >= __StartingWeekNum,'Date'[Week Number] - __StartingWeekNum + 1,

__EndingWeekNum + 'Date'[Week Number]

)

Much appreciated. 

[amitchandak]

@ClaireBear , refer if my video on FY week can help.

https://www.youtube.com/watch?v=euIC0dgGTNM

Another way is

mod(quotient(datediff(min(Date[Date]), [Date], day),7),52)+1

Assuming min(Date[Date]) is start of the calendar is a monday in july

[ClaireBear]

This solution is showing January as week 1 and not July. 

Thanks

[V-pazhen-msft · Accepted solution]

@ClaireBear 
There is no straight forward way in power bi to set the custom weeknumber, you might need  to work with some dax. 

Try create the following column, check attached pbix for detail.

Week number = 
var WkNo= RANKX(FILTER('Table',WEEKNUM([Date],2)>=27 &&WEEKNUM([Date],2)<=53),WEEKNUM([Date],2),,ASC,Dense)
Return
IF(WEEKNUM([Date],2)>=27&&WEEKNUM([Date],2)<=53,WkNo,WEEKNUM([Date],2)+26)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.