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 No TOTAL SALES YTD 2021 ...
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

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 !