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...
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

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.