Find Week Number of Specific Month in Custom Date Table

[drewbrannan]

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.

Date Table PBIX 

Thanks

[amitchandak]

@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

[SampaSim · Accepted solution]

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 + 1

you 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