Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Max common week

Hello Everyone,
How to unify the week numbers and set the number of the last common week for all categories?

Below is what some of the data looks like:

CATEGORY WEEK NoTOTAL SALES YTD 2021MAX COMMON WEEK ?
A1343M13
B162M13
C133M13
D146M13

 

PS: - the week number from calendar table is updated automatically.

      - the min common week is 1

      - the function : Max common week= Calculate(max('Dim Calendar'[No Week]),ALLSELECTED('Table'[Category])) is not working

Thanks in advance ! 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    What's the calculation logic of Max common week? If you have the table with below data, what is Max common week? It is still 13 or other value? Could you please some original data in Table and your expected result with examples? Thank you.

    If Max common week is 12, then you can create a measure as below. Please find the attachment for the details.

     

    Measure =
VAR _tab =
    SUMMARIZE (
        'Table',
        'Dim Calendar'[Year],
        'Dim Calendar'[No Week],
        "numberofweek",
            CALCULATE (
                COUNT ( 'Table'[CATEGORY] ),
                FILTER (
                    ALL ( 'Table' ),
                    YEAR ( 'Table'[DATE] ) = SELECTEDVALUE ( 'Dim Calendar'[Year] )
                        && WEEKNUM ( 'Table'[DATE] ) = SELECTEDVALUE ( 'Dim Calendar'[No Week] )
                )
            )
    )
RETURN
    MAXX (
        FILTER ( _tab, [numberofweek] = MAXX ( _tab, [numberofweek] ) ),
        [No Week]
    )

     

    Best Regards

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a measure like

    Max common week= Calculate(max('Dim Calendar'[No Week]),ALLSELECTED('Table'))

     

    Check join between Dim Calendar and table is correct

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak  !

      Thank you for your feedback, but it still doesn't work. I think I need to change the max function.
      Here is what I want in detail:

      Category            Year      What i have (No week)  with the MAX function        What i need

      A                        2020                52                                     52                                   52

      A                        2021                13                                     16                                   13

      B                        2020                52                                     52                                   52

      B                        2021               16                                      16                                   13

      C                      2020                 52                                       52                                   52

      C                      2021                14                                       16                                    13

      D                     2020                 52                                        52                                   52

      D                     2021                 13                                       16                                    13

      What do you think?

      Many Thanks !

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    What's the calculation logic of Max common week? If you have the table with below data, what is Max common week? It is still 13 or other value? Could you please some original data in Table and your expected result with examples? Thank you.

    If Max common week is 12, then you can create a measure as below. Please find the attachment for the details.

     

    Measure =
VAR _tab =
    SUMMARIZE (
        'Table',
        'Dim Calendar'[Year],
        'Dim Calendar'[No Week],
        "numberofweek",
            CALCULATE (
                COUNT ( 'Table'[CATEGORY] ),
                FILTER (
                    ALL ( 'Table' ),
                    YEAR ( 'Table'[DATE] ) = SELECTEDVALUE ( 'Dim Calendar'[Year] )
                        && WEEKNUM ( 'Table'[DATE] ) = SELECTEDVALUE ( 'Dim Calendar'[No Week] )
                )
            )
    )
RETURN
    MAXX (
        FILTER ( _tab, [numberofweek] = MAXX ( _tab, [numberofweek] ) ),
        [No Week]
    )

     

    Best Regards