Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Display counter based on specific max date

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:

DateCounter
01/01/20201
02/02/20201
02/02/20201
03/03/20201
03/03/20201
03/03/20201

 

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
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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.

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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.