Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help with creating week number / week sequence number

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! 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

7 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        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's avatar
      Anonymous
      Not applicable
      6 years ago

      Its just that I need a solution like this

       

       

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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.