I want to calculate week number of the month ( week 1, week 2, week 3, week 4 & week 5 )

[vrushabjain510]

I want to calculate week number of the month ( week 1, week 2, week 3, week 4 & week 5 ). 

I tried using the formula 

Week Num = CONCATENATE("Week-",WEEKNUM('Date'[Date]) - WEEKNUM(DATE(YEAR('Date'[Date]),MONTH('Date'[Date]), 1)) + 1)

but this formula is showing week 6 as well. 

I need only 5 weeks in month and the week starts from Sunday.

Please help me in this.

Thanks in advance

[v-kelly-msft · Accepted solution]

Hi  @vrushabjain510 ,

Create 3 columns as below:

Sunday = IF(WEEKDAY('Table'[Date],1)=1,1,BLANK())

rank = 
var _rank=RANKX(FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Sunday]<>BLANK()),'Table'[Date],,ASC,Dense)
return
IF('Table'[Sunday]<>BLANK(),_rank,BLANK())

weeknum = 
var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Sunday]=1))
Return
IF('Table'[Sunday]=1,"week"&'Table'[rank],
   IF(_maxdate=BLANK(),BLANK(),
      "week"&CALCULATE(MAX('Table'[rank]),FILTER(ALL('Table'),'Table'[Date]=_maxdate))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Thejeswar]

Hi @vrushabjain510 ,

We can get the weekno using a WEEKNUM() function. Ex. given below

WEEKNUM(Table_Name[Date],1)

[amitchandak]

@vrushabjain510 , Create columns like

month week No = quotient(datediff([eomonth([date],-1)+1,[date],day),7)+1

Week name = if([month week No] < 4, "Week " & [month week No] , "Week 4 & Week 5")