Help with Calendar Week Numbers - No Date

[msylv13]

Hello, 

I have a dataset that provides the calendar week number (i.e. 1 thru 52) and the year. With this data I am facing two issues. First, I have visuals with the week numbers as the x-axis values. I will need these to display week 1 after week 52 when the next year begins and am not sure how to build that capability. The other problem I am facing is that I have created a measure for calculating the rolling 4 week average based upon the week number. Essentially, this measure ranks the week numbers in descending order and calculates from there. I assume it will fail when week 1 of next year becomes available. Here is a sample DAX for the rolling average:

CFS_4wk_num =

VAR MyIndex =  selectedvalue('Rolling Table'[Index])

VAR myResult =

    AVERAGEX(

        FILTER(

            ALL('Rolling Table'),

            'Rolling Table'[Index] > MyIndex-4  &&

            'Rolling Table'[Index] <= MyIndex

           ),'Rolling Table'[CFS]

           )

RETURN FIXED(myResult,2)    

Any help would be greatly appreciated! I can provide additional context as needed. 

[v-jianboli-msft · Accepted solution]

Hi @msylv13 ,

Based on your description, I have created a simple sample:

Please try:

Column =
VAR _a =
    AVERAGEX (
        FILTER (
            'Rolling Table',
            [Index] <= EARLIER ( 'Rolling Table'[Index] )
                && [Index]
                    > EARLIER ( 'Rolling Table'[Index] ) - 4
                && [Year] = EARLIER ( 'Rolling Table'[Year] )
        ),
        [CFS]
    )
VAR _b =
    IF (
        [Index] >= 4,
        _a,
        AVERAGEX (
            FILTER (
                'Rolling Table',
                ( [Year] * 100 + [Index] )
                    > (
                        ( EARLIER ( 'Rolling Table'[Year] ) - 1 ) * 100 + 48
                            + EARLIER ( 'Rolling Table'[Index] )
                    )
                    && ( [Year] * 100 + [Index] )
                        <= (
                            EARLIER ( 'Rolling Table'[Year] ) * 100
                                + EARLIER ( 'Rolling Table'[Index] )
                        )
            ),
            [CFS]
        )
    )
RETURN
      _b

change the format:

Output:

Then apply it to the visual:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msylv13]

Works perfectly as far as I can tell. Thank you!

[v-jianboli-msft]

Hi @msylv13 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msylv13]

The question regarding the visual was solved. However, I am still searching for support on my question regarding the DAX in my measure. 

[Syk]

You can put both the Year and Week number in the x-axis then expand the data by clicking the highlighted button in the screenshot. Make sure to sort your axis by the year/week number and set to ascending to see it as expected!

[msylv13]

Thank you! This works perfectly. I do still have a remaining issue to solve in my initial post, so I am unsure if I shold mark this as a solution.