Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Calendar Week Numbers - No Date

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. 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi Anonymous ,

     

    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.

6 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    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!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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. 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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