Need help with creating week number / week sequence number

[Anonymous]

Hi all! Hope you guys help me with my problem about week number.

Details are:

I have dataset consists of 2 types such as (request or incident), created date and time.

What I need to do is to make the weekday number into a "WEEK NUMBER" which I will use to determine the Week1 to Week4.

For Example:

Oct 28 (Monday) to Nov 3 (Sunday) as Week1

Nov. 4 (Monday) to Nov 10 (Sunday)as Week2

Nov. 11 (Monday) to Nov 17 (Sunday) as Week3

Nov. 18 (Monday) to Nov 24 (Sunday) as Week4

and so on until I reach the last week of the month

The picture below is the date I use (Created, one of my dataset)

This is my weekday number. I already tried  to filtered rows, added index, Expand d'added index and other ways but still doesn't work.

Questions:

1. What are the steps or queries I need to do to get the Week Number?

2. What are the other steps / logic / idea I need to do?

Thank you! 

[v-frfei-msft]

Hi @Anonymous ,

We can achieve that by DAX as well.

weekinmonth =
VAR a =
    ADDCOLUMNS (
        'Table',
        "week", 1 + WEEKNUM ( 'Table'[Date] )
            - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ) )
    )
VAR maxw =
    MAXX (
        FILTER (
            a,
            'Table'[Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
                && 'Table'[Date].[Month] = EARLIER ( 'Table'[Date].[Month] )
        ),
        [week]
    )
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
                && 'Table'[Date].[Month] = EARLIER ( 'Table'[Date].[Month] )
                && (
                    1 + WEEKNUM ( 'Table'[Date] )
                        - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ) ) = maxw
                )
        )
    )
RETURN
    IF (
        1 + WEEKNUM ( 'Table'[Date] )
            - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ) ) <> maxw,
        1 + WEEKNUM ( 'Table'[Date] )
            - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ) ),
        IF (
            1 + WEEKNUM ( 'Table'[Date] )
                - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ) ) = maxw
                && c = 7,
            1 + WEEKNUM ( 'Table'[Date] )
                - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ) ),
            1
        )
    )

For more details, please check the pbix as attached.

[Jimmy801]

Hello @Anonymous ,

don't know if I got you right...

You don't need the weeknumber of the year, but the weeknumber of the month? Is this right?

Jimmy

[Anonymous]

Its just that I need a solution like this

[Anonymous]

Yes. That's right. I just need the week number of the month. Is there any way to do it? 

[Jimmy801 · Accepted solution]

Hello @Anonymous 

sure it's possible. Everything is possible with Power Query 🙂

See this example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2tTDTMTVQitUBcczMLXSMTKEcc3MDHVMEx1LH2BjKsTA00bEA6okFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangeNumberToDate = Table.TransformColumns(Source,{{"Date", each DateTime.From(Number.From(_)), type datetime}}),
    AddedCustomColumn = Table.AddColumn(ChangeNumberToDate, "Week of month", each Date.WeekOfYear
        (
            [Date]
        )
        -
        Date.WeekOfYear
        (
            #date
            (
                Date.Year([Date]),
                Date.Month([Date]),
                1
            )
        )
        +1)
in
    AddedCustomColumn

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Hi Jimmy!

A little again, is it okay? The solution you gave, it work but there's one more problem because it just happen that it start on sunday but what I need is week of month that start on monday.

Is there any way for that to happen? Hope you can help me. I'm sorry, I'm just a beginner.

Thank you! 

[Jimmy801]

hello @Anonymous 

you should mention this always at the beginning. However, I adapted the code to your needs

However.. in dates in weeks that doesn't start at monday you will have week 0

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2tTDTMTVQitUBcczMLXSMTKEcc3MDHVMEx1LH2BjKsTA00bEA6okFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ChangeNumberToDate = Table.TransformColumns(Source,{{"Date", each DateTime.From(Number.From(_)), type datetime}}),
    AddedCustomColumn = Table.AddColumn(ChangeNumberToDate, "Week of month", each 
    let
        NormalweekNumber = 
        Date.WeekOfYear
        (
            [Date]
        )
        -
        Date.WeekOfYear
        (
            #date
            (
                Date.Year([Date]),
                Date.Month([Date]),
                1
            )
        )
        +1,
        Monday = 
        if Date.DayOfWeek
        (
            #date
            (
                Date.Year([Date]),
                Date.Month([Date]),
                1
            ),
            Day.Monday
        )= 0 then 0 else -1
    in 
        NormalweekNumber + Monday)
in
    AddedCustomColumn

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy