Forum Discussion
Week Number calculated coloumn
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
vjnvinod ,
Please find the attached file
6 Replies
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
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
vjnvinod ,
Please find the attached file
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
