MAX Count of Date Measure

[Creative_tree88]

Hi - I have the following date (abbreviated sample).  I count by Event Key (table is called 'WBR').  I'd like to produce a measure which calculates the highest maximum count, across all the dates.  I'd then like to somehow display the Date, to which this maximum count occurs on.

In my sample, the Maximum count (based on Event Key) is 6, and this occured on 01/01/2022.

Really appreciate your help on what it probably quite simple but it's tying me up in knots at the moment!  Many thanks everyone.

Event Key	Stat Date
4432119	01/01/2022
4798904	01/01/2022
5426796	01/01/2022
5483090	02/02/2022
5289897	02/03/2022
4245429	02/03/2022
4366550	04/01/2022
7317690	05/01/2022
4494973	06/01/2022
6560468	07/01/2022
4722334	08/01/2022
5770131	09/03/2022
5323040	10/02/2022
6033420	01/01/2022
5210188	01/01/2022
4364367	01/01/2022

[v-zhangti · Accepted solution]

Hi, @Creative_tree88 

You can try the following methods.
Measure:

Count = CALCULATE(COUNT('Table'[Stat Date]),ALLEXCEPT('Table','Table'[Stat Date]))

Max Count = MAXX(ALL('Table'),[Count])

Max Count Date = CALCULATE(MAX('Table'[Stat Date]),FILTER(ALL('Table'),[Count]=[Max Count]))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[YukiK]

MAXX(
    VALUES('WBR'[Stat Date]),
    CALCULATE( COUNTROWS('WBR') )
)

Give it a thumbs up and accept as solution if this is helpful!

[Creative_tree88]

@YukiK  - that works.  But how do I create a second measure to show me the date that this MAX corresponds to?  In my example above it would be 01/01/2022.  Many thanks!

[YukiK]

Your Measure = 
VAR _Cnt = CALCULATE( COUNTROWS('WBR') )
VAR _MaxDate = 
FILTER(
    VALUES('WBR'[Stat Date]),
    CALCULATE( COUNTROWS('WBR') ) = _Cnt
)
RETURN
_MaxDate

This should work, though, I'd have "COUNTROWS('WBR')" as a separate measure and reference it in other measures.

[Creative_tree88]

@YukiK  - this doesn't seem to work.  Power BI doesn't return any date, just 'Blank'. Any ideas?

[YukiK]

Oops. I just referenced a wrong thing. Replace the first variable with this one:

VAR _Cnt = [Max Cnt Measure you just created]