Forum Discussion

drewbrannan's avatar
drewbrannan
Frequent Visitor
6 years ago
Solved

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... Fin...
Need Help
Tips and Tricks
  • SampaSim's avatar
    SampaSim
    6 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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