Max common week

[Anonymous]

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	MAX COMMON WEEK ?
A	13	43M	13
B	16	2M	13
C	13	3M	13
D	14	6M	13

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 ! 

[v-yiruan-msft · Accepted solution]

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]

@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]

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 !