Dynamic week for a different date range

[Anonymous]

Hey guys!

I have a very interesting challenge, both for me and for you.

I need to create a DAX that gives me the week number within the month range. However, the month range is different. Here in the sector, the interval 21 of each month until the 20th of the other month is considered instead of 01 and 30, respectively.

With that, I need to create a DAX that sees that the interval 21 of a month until the 20th or 21st of another month refers to 1 month, so that it calculates the week of this month interval.

Example: the month of July is considered from 06/21/2021 until 07/21/2021. For this interval, week 1 would take from 06/21/2021 to 06/27/2021 and so on.

I made this calculation to try to help me:

Week = WEEKNUM('dCalendar'[Date]) - WEEKNUM(FORMAT('dCalendar'[Date], "mm/yyyy")) + 1

This formula shows the week within the month range, however, it does not consider the month starting from the 21st to the 21st of the other month.

Here is my difficulty in making the DAX that returns the week of this break.

can you help me?

Thank you all!

[v-luwang-msft]

Hi @Anonymous ,

Try the following dax to create new column:

month = 
IF (
    IF (
        DAY ( dCalendar[Date] ) < 27,
        MONTH ( dCalendar[Date] ) - 1,
        MONTH ( dCalendar[Date] )
    ) <> 0,
    IF (
        DAY ( dCalendar[Date] ) < 27,
        MONTH ( dCalendar[Date] ) - 1,
        MONTH ( dCalendar[Date] )
    ),
    12
)

Week = 
WEEKNUM ( 'dCalendar'[Date].[Date] - 26 )
    - WEEKNUM ( FORMAT ( 'dCalendar'[Date] - 26, "mm/yyyy" ) ) + 1

Then you will get the below:

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Best Regards

Lucien

[Anonymous]

In this sample base, you will notice that the column "Month/year" already references what is considered the month interval.

You will also see how the logic of the week interval is, considering the 21st always as the beginning of the month and considering the week always from Tuesday to Wednesday.

OBS: I couldn't put the sample base, so I sent the print

Mês/Ano	Data
julho de 2021	21/06/2021
julho de 2021	22/06/2021
julho de 2021	23/06/2021
julho de 2021	24/06/2021
julho de 2021	25/06/2021
julho de 2021	26/06/2021
julho de 2021	27/06/2021
julho de 2021	28/06/2021
julho de 2021	29/06/2021
julho de 2021	30/06/2021
julho de 2021	01/07/2021
julho de 2021	02/07/2021
julho de 2021	03/07/2021
julho de 2021	04/07/2021

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

According your picture ,adjust like below:

Week = IF(
WEEKNUM ( 'dCalendar'[Date].[Date] - 20 ,13)
    - WEEKNUM ( FORMAT ( 'dCalendar'[Date] - 20, "mm/yyyy" ),13 ) <=0,1,WEEKNUM ( 'dCalendar'[Date].[Date] - 20 ,13)
    - WEEKNUM ( FORMAT ( 'dCalendar'[Date] - 20, "mm/yyyy" ),13 ) )

month = 
IF (
    IF (
        DAY ( dCalendar[Date] ) < 21,
        MONTH ( dCalendar[Date] ) - 1,
        MONTH ( dCalendar[Date] )
    ) <> 0,
    IF (
        DAY ( dCalendar[Date] ) < 21,
        MONTH ( dCalendar[Date] ) - 1,
        MONTH ( dCalendar[Date] )
    ),
    12
)

Final get: the 21st always as the beginning of the month and considering the week always from Tuesday.But in your picture ,I see the week start from  Tuesday and end  is Monday,not Wednesday.

Or week use this column，6/21 will return blank:

Week3 = IF(
WEEKNUM ( 'dCalendar'[Date].[Date] - 20 ,13)
    - WEEKNUM ( FORMAT ( 'dCalendar'[Date] - 20, "mm/yyyy" ),13 ) <=0,BLANK(),WEEKNUM ( 'dCalendar'[Date].[Date] - 20 ,13)
    - WEEKNUM ( FORMAT ( 'dCalendar'[Date] - 20, "mm/yyyy" ),13 ) )

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[PaulDBrown]

does this have to be a measure for some specific reason? Do you have a Date Table? 
if you have a date table, you can create a calculated column along the lines of:

Month range =

IF(DAY(Date table[Date) < 21, WEEKNUM(Date table[Date]),

WEEKNUM(Date table[Date]) + 1)

it should also work as a measure if the Date table[Date] field is in the visual

[Anonymous]

there is a base that offers the dates.

What I need is: when DAX comes across the days 07/21/2021 (I'll use this number as an example), it knows that when it comes on 07/27/2021 it will be considered 1 WEEK. Then, when he comes across the day 07/28/2021, he needs to understand that when there is 08/03/2021 it will be considered 2 WEEKS (Note that he takes the number of days in the month, divide by 7 to know the amount of weeks, but regardless of whether the day appears in another month ).

And this logic should be done month by month.

Note that I want the break of the week in the month, and not the rush week (week 18, week 19...).

Example of what I want: for month X, it has 4 weeks; for month y, it has 5 weeks, etc...

Basically, it's like I said this:
"From now on, I will change the reference of what is considered the 1st of each month and its respective weeks. Now, the reference to the 1st of each month will start from the 21st".

[PaulDBrown]

please provide a sample PBIX file, or sample data (not an image), a depiction of the model and a depiction of what you wish to show.

In other words, please follow the recommendations outlined in this thread:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...