Week Number calculated coloumn

[vjnvinod]

Hi,

i am using the below calculated coloumn to arrive at week number, but the output what its is giving me is based on Calendar year

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

I need output based on Fiscal year(1st July 2019- 30 June 2020)

what will be my forumula to arrive that?

also i would need a Week coloumn based on my month

for example July, 1st week Monday to Friday, show me that as Wk1 and so on till Wk4 or Wk5 and again for july same as Wk1 to Wk5 etc and so on

[v-eachen-msft]

Hi @vjnvinod ,

If you want to create a custom fiscal year calendar, you could refer to this similar case:

https://community.powerbi.com/t5/Desktop/Custom-Fiscal-Year-Calendar/m-p/85228

[amitchandak]

Create a column start of year

start of year = startofyear([Date],"06/30")

Weekno = quotient(datediff([start of year],[Date],day),7) +1

OR

Weekno = quotient(datediff([start of year],[Date],day)+1,7) +1

You can move year start to week start like

Week Start date = 'Table'[start of year]-1*WEEKDAY('Table'[start of year],2)+1

https://docs.microsoft.com/en-us/dax/quotient-function-dax

[vjnvinod]

@amitchandak 

here is the calendar

https://drive.google.com/file/d/12EIubQ81bBk1IhcGPE8FKtYXZRucvs9k/view?usp=sharing

and also the pbix

https://drive.google.com/file/d/1kchwmBCutj5PCnF7BRKsTA03hYpIGKtw/view?usp=sharing

thanks so much for your help

[amitchandak · Accepted solution]

@vjnvinod ,

Please find the attached file

[vjnvinod]

@amitchandak 

Great stuff, it worked out:)

[amitchandak]

@vjnvinod , Thanks.

This one is a good example of 5-4-4 period.