Calculate The max By year For all the user

[OmarSek75]

The first Step I calculate How many order has every user in the year.
Te idea here is if the user has one order or more in a month I will count as 1. Means if the user has order all the month , the resut will be 12.

This is the formula : 

UniqueMonthsPerYear =

VAR NumberOccurencePerYear =

    COUNTROWS (

       SUMMARIZE (

        'ft_qualityesod',

       'DateTable'[MonthYear],

       ft_qualityesod[user_email]

    )

)

RETURN

IF (ISBLANK(NumberOccurencePerYear),

    0,

    NumberOccurencePerYear)

Then I want  to do some conditional formating , so I thought about creating a new measure where I calculate the max every year then divide the value on the max .... etc 
Now I have problem calculating the max , this is my formula : 

Number Declaration Conditional formating =

var MaxMonthsPerYear =

CALCULATE(

    MAXX(

        SUMMARIZE(

            ALL('ft_qualityesod'),

            'DateTable'[Year],

            'ft_qualityesod'[user_email],

            "UniqueMonths", [UniqueMonthsPerYear]

        ),

        [UniqueMonths]

    )

)

RETURN MaxMonthsPerYear
This is an image of result : 


I don't understand why I have 12 all the time, for 2024 as we are in juin , I'am supposed to have 4 (AS my dara are until Avril).

I need help to resolve this problem.Thanks

[v-jianpeng-msft · Accepted solution]

Hi, @OmarSek75 

Since you used ALL, all filters are cleared. This will take the maximum value in the UniqueMonths column of the summarize virtual table. If you want to keep the year filter, you should change your DAX to:

Number Declaration Conditional formating =
var MaxMonthsPerYear =
CALCULATE(
    MAXX(
        SUMMARIZE(
            ALLEXCEPT ('ft_qualityesod','DateTable'[Year]),
            'DateTable'[Year],
            'ft_qualityesod'[user_email],
            "UniqueMonths", [UniqueMonthsPerYear]
        ),
        [UniqueMonths]
    )
)
RETURN MaxMonthsPerYear

AllEXCEPT retains the year filter so that the maximum value for each year is correctly calculated.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[BeaBF]

@OmarSek75 Hi! Can you paste the data on which you calculate these formulas?

BBF

[OmarSek75]

• I can't because it's some confidential data.