How to derive week in month with Monday to sunday weekly bucket

[kumarp]

Hi,

Can any one please help me to derive week in month field with Monday to sunday weekly bucket from Date field.

Thanks in advance.

[kumarp]

Hi,

Can any one please help me on below requirement. I have derived week in month using below formula but its taking Monday to Sunday bucket. Please help me on this.

Week in month = 1 + WEEKNUM ( INCOMEDETAILVIEW_MH[Booked Date] )-WEEKNUM( STARTOFMONTH (INCOMEDETAILVIEW_MH[Booked Date]))

[Greg_Deckler]

@kumarp You can use WEEKNUM([Date], 2) for Monday - Sunday week. From there, you could grab the MIN for the current month and subtract that + 1 from the WEEKNUM value.

[kumarp]

Hi @Greg_Deckler 

I have tried below but week2 is getting wrong . I am expecting august 7th (Monday) to august 13th (Sunday) as week2 but getting august 13th on week3. Please can you help me on this.

I need to show week in month for all the months.

Week in month = 1+WEEKNUM ( INCOMEDETAILVIEW_MH[Booked Date].[Date],2)-WEEKNUM( STARTOFMONTH (INCOMEDETAILVIEW_MH[Booked Date]))

[v-yueyunzh-msft · Accepted solution]

Hi， @kumarp 

According to your description, you want to get the week in every month and the logic is from Monday to Sunday.

Here are the steps you can refer to :
(1)If you want to create a calcualte column, you can use this dax code:

Column 2 = var _date = [Date]
var _first_of_month =  DATE( YEAR(_date) , MONTH(_date) ,1)
var _end_of__month =  EOMONTH(_date,0)
var _t = ADDCOLUMNS( CALENDAR(_first_of_month , _end_of__month) , "WEEK" , WEEKDAY([Date],2))
var _count=COUNTROWS( FILTER(_t , [Date]<_date && [WEEK] = 7))
return
_count+1

(2)If you want to create a measure , you can use this dax code :

Measure = var _date = MAX('Table'[Date])
var _first_of_month =  DATE( YEAR(_date) , MONTH(_date) ,1)
var _end_of__month =  EOMONTH(_date,0)
var _t = ADDCOLUMNS( CALENDAR(_first_of_month , _end_of__month) , "WEEK" , WEEKDAY([Date],2))
var _count=COUNTROWS( FILTER(_t , [Date]<_date && [WEEK] = 7))
return
_count+1

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly