Forum Discussion
Find Week Number of Specific Month in Custom Date Table
I have a custom date table that aligns with my company's fiscal months and years. It is a 4-4-5 calendar. What I need to be able to do is figure out the week number in the current month. Ie... Financial week number 5 is week 1 in February and so on. I have been searching for days and while I've found a lot for standard date tables, I can't seem to find anything for my specific use case. It would be great if someone could point me in the right direction.
Thanks
Hi drewbrannan ,
if I understood well your problem you want to add a column which contains the information about the relative week of a month. Let's suppose your date table is named 'Calendar', then you can add a calculated column like this
Week of Month = VAR selected_week = 'Calendar'[Cal Week Number] VAR selected_month = 'Calendar'[Cal Month Number] VAR first_week_of_month = CALCULATE( MIN('Calendar'[Cal Week Number]), ALL('Calendar'), 'Calendar'[Cal Month Number] = selected_month ) RETURN selected_week - first_week_of_month + 1you can also have a look here
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
If your problem is solved then please accept this reply as a solution.
Thank you
SampaSim
2 Replies
Hi drewbrannan ,
if I understood well your problem you want to add a column which contains the information about the relative week of a month. Let's suppose your date table is named 'Calendar', then you can add a calculated column like this
Week of Month = VAR selected_week = 'Calendar'[Cal Week Number] VAR selected_month = 'Calendar'[Cal Month Number] VAR first_week_of_month = CALCULATE( MIN('Calendar'[Cal Week Number]), ALL('Calendar'), 'Calendar'[Cal Month Number] = selected_month ) RETURN selected_week - first_week_of_month + 1you can also have a look here
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
If your problem is solved then please accept this reply as a solution.
Thank you
SampaSim
- amitchandak
Super User
drewbrannan , So if you need week number of the month
Week Start date = [Date] -weekday([Date]) +1
Min Week of Month = minx(filter(Date, [MonthYear] =earlier([Month Year])),[Week Start date])
Week of Month = Quotient([Min Week of Month], [Date],7)
In the same way, we identify a min week of the year, first divide by 13 and then by 4, keeping 13 as 5.
You can refer -https://www.youtube.com/watch?v=JqVnqMLGWDY
