Weeknum of month and based on Monday as new week

[NickProp28]

Dear Community,

Taking Nov 2020 as example, would like to get help on weeknum (1-4) monthly and based on Monday as a new week.

1 Nov (Sunday) - week 1
2 Nov (Mon) - 8 Nov (Sun) - week 2
9 Nov (Mon) - 15 Nov (Sun) - week 3
16 Nov (Mon) - 22 Nov (Sun) - week 4
23 Nov (Mon) - 29 Nov (Sun) - week 5
30 Nov (Mon) - week 6

Had go thru some previous post and get this DAX, 

weekinmonth = 1+WEEKNUM ('Calendar'[Date])-WEEKNUM( STARTOFMONTH ('Calendar'[Date]))

But this is follow the calendar and I would like to customized it based on Monday.

Appreciate any help

[amitchandak · Accepted solution]

@NickProp28 , Create following in your date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

min week start of month = minx(filter('Date',[Month Year] =earlier([Month Year])),[Week Start date])
week of month = datediff([min week start of month],[date],Week)

or

week of month = quotient(datediff([min week start of month],[date],day),7)+1

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[NickProp28]

Dear @amitchandak ,

Thanks for ur response.

It works perfectly for me.