Return the date where the sum of values is maximum

[Anonymous]

Hi, 

I have a table with some purchases. Each purchase has a date, a customer and a value:

Date	Customer	Value
02/05/2022	Kevin	53
02/05/2022	Mary	12
02/05/2022	Louise	43
03/05/2022	Tom	19
03/05/2022	Lewis	24
04/05/2022	Richard	28
04/05/2022	Rachel	52

I'd like to build a visualization that returns the date where the sum of Value column is the maximum. 

The sum of purchases for 02/05/2022 is 108 (53+12+43).

The sum of purchases for 03/05/2022 is 43 (24+19)

The sum of purchases for 04/05/2022 is 80 (28+52).

So, in my card visualization, I want to show "02/05/2022", because 108>80>43. 

At the moment, I have achieved the value of the sum of purchases corresponding to this date, with this DAX formula:

Sum Maximum Value = 

    MAXX(
        SUMMARIZE(
            ALL('Table') ,
            Table[Date]
        ) ,
        CALCULATE(Sum('Table'[Value])
    ) )

But I'm not able to build a measure that returns the Date value corresponding to this Sum Maximum Value. 

Could you help me?

Regards

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Try it.

Sum Maximum Value = MAXX( SUMMARIZE('Table',[Date],"1",SUM([Value])),[1])

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL]

For fun only, make it dynamic.

 Also with mighty Excel worksheet formula,

[amitchandak]

@Anonymous , Use TOPN

Create total measure

Total = CALCULATE(Sum('Table'[Value])

Top total with date in visual
Top1 total= CALCULATE([Total],TOPN(1,allselected(Table[Date]),[Total] ,DESC),VALUES(Table[Date]))

Top date to display
Top1 Date =CALCULATE(max(Table[Date])),TOPN(1,allselected(Table[Date]),[Total] ,DESC),VALUES(Table[Date]))