Custom week of month in date table

[patrickessa]

Hello, I've created a date table in an attempt to create a Year/Month/Week/Date Hierarchy. The problem I have is with the weeks, of course...

In every other Q&A that I've studied on the forum, the solution works on the framework of days-of-the-week, i.e. Sunday through Saturday, or Monday through Sunday. 

But I need a week number column based on the day of the month (i.e. take whichever number it is, 1-31, divided by 7 days in a week, to give a result somewhere between 1-5). For example, days 1-7 would always give a result = 1, days 8-14 would always give result = 2, etc.

Here's what I mean, the two real columns are the ones I've managed to create, and the far-right column with red text ("WeekNum") is what I actually want a DAX formula to do:

Thanks in advance!

[Phil_Seamark · Accepted solution]

Hi @patrickessa

Does this do what you want?

Column = INT((DAY([Date])-1)/7)+1

[patrickessa]

Thanks @Phil_Seamark! 

[Phil_Seamark]

PS, don't try and create a YEAR/MONTH/WEEK hierarchy.  They just don't work well together.

Create a YEAR/MONTH, or a YEAR/WEEK - but in the later case make sure the YEAR aligns with your first/last weeks