WeekNumberIndex to display original week number or incremental week number if more than 1 year

[hazziquen]

Hi All,

I have a chart that MUST be a line chart, due to a requirement to display rolling averages using a custom rolling average measure. And my X axis is in week numbers, between 1 - 52. Everything is working fine, until 2024 hits, and I notice that my is chart is no longer valid to monitor with respect to time, as 2023 and 2024 data is now merged together.

So now I am considering to use WeekNumberIndex instead. I'd like to use a Year slicer, and if only 1 year is selected, display the original week number. And if more than 1 year is selected, then the week number will incremental past 52.

Currently I have this calculated column formula:

WeekNumberIndex =
VAR SelectedYears = VALUES('Table'[Year])
RETURN
IF(
    COUNTROWS(SelectedYears) > 1,
    'Table'[Week Number] +
        52 * (
            'Table'[Year] -
            MINX(FILTER(ALL('Table'), 'Table'[Year] IN SelectedYears), 'Table'[Year])
        ),
    'Table'[Week Number]
)

This worked when I selected 2023 only, and 2023 + 2024 and it adds incrementals to the second year onwards. However, selecting only the second year still shows 53, 54, ... instead of 1, 2, ...

I tried alot of different formulas, like:

WeekNumberIndex =

    'Table'[Week Number] +

    IF(

        COUNTROWS(VALUES('Table'[Year])) > 1,

        52 * (

            'Table'[Year] -

            CALCULATE(

                MIN('Table'[Year]),

                FILTER(

                    ALL('Table'),

                    'Table'[Year] <= EARLIER('Table'[Year])

                )

            )

        ),

        0

    )

And

WeekNumberIndex =

    VAR SelectedYears = VALUES('Table'[Year])

    RETURN

    'Table'[Week Number] +

    IF(

        COUNTROWS(SelectedYears) > 1,

        52 * (

            'Table'[Year] -

            MIN('Table'[Year])

        ),

        0

    )

They all work the same and I was not able to use the original week number when only 2024 is selected.

Where do you think my approach is wrong? Thanks in advance.

[hazziquen]

Hi @MFelix ,

Thank you for your reply. Putting Year in the legend is still not what I was intending, because then the new year will start at the beginning of the chart. I wanted every new data point to always be after the previous ones. Which is why creating a weeknumberindex was my initial thought.

[MFelix · Accepted solution]

Have you tried a hierarchy on the x-axis with the year and the week?

Than will give you continuous values passing trough the years.

[hazziquen]

Hi @MFelix ,

Well I'll be damned. This actually worked and somehow I never thought of using hierachy!! Such a simple solution. Cant believe I thought of creating indexes first before trying this!! You're a life saver.

Thanks, this is good enough for what I need to do. It does however stretches the X axis and make the chart scrollable instead of everything in one chart. Its not ideal, but also not a big deal for me. 

I think i'll make do with this solution. Thanks!!

[MFelix]

Hi @hazziquen ,

Do you have the legend of the year on your chart? since you are using the week numbers if there is no year context the number will be summed together.