Display counter based on specific max date

[Anonymous]

Hello,

I am currently in trouble to be able to display a specific graph.

I have a table with Dates and multiple rows with counter:

Date	Counter
01/01/2020	1
02/02/2020	1
02/02/2020	1
03/03/2020	1
03/03/2020	1
03/03/2020	1

I have calculated average count to display it through time :

Average = Averagex (Values([Date]);sum([Counter]))

This is what it looks like when I display the average value and the day at the same time.

I am now trying to find the Day where this average is at its maximum value. I could only get the max average value so far using this : 

MAXX(

    KEEPFILTERS(VALUES([Day]));

    CALCULATE([Average])

)

I would like to get the date related to this value, to be able to filter a graph with this date.

My first question is : How can I get this date ?

Second question : Is it possible to filter a graph with a date coming from a measure ?

Thanks for your help ! 🙂

Antoine

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following measures:

Max of Averages= MAXX(ALLSELECTED('Table'[Date]);[Average])


Maximum Date of Maximum average = 
 (
    CALCULATE (
        MAX ( 'Table'[Date] );
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ); 'Table'[Date]; "@Average_Value"; [Average] );
            [@Average_Value] = [Max of Averages]
        )
    )
)


Filter = if(SELECTEDVALUE('Table'[Date]) = [Maximum Date of Maximum average];1 ; 0)

Now use the Filter  to filter out your visualization.

Check PBIX file attach, I have chan the data to get the maximum on 2/2 to check if my formulas where correct.