Bar chart based on the latest date of a slicer

[Anonymous]

Hi,

  I need to plot a top 5 bar chart. Although the user can select multiple dates, but the bar chart will only show values for the latest date.  

  For example, my data is:

The date column will be a slicer. If user select only one date, then plot the top 5 values for that date. But if user select two or more dates, the bar chart will only plot the top 5 values for the latest date selected.

Examples:

- If user select "31-Mar-19" only: Plot A(310) C(280) D(7) E(30) F(25)

- If user select "31-Jan-19" and "31-Mar-19": Plot A(310) C(280) D(7) E(30) F(25), as "31-Mar-19" is the latest date selected

- If user select "31-Jan-19" and "28-Feb-19": Plot Plot A(160) C(300) D(70) E(80) F(40), as "28-Feb-19" is the latest date selected. 

Thanks

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[parry2k · Accepted solution]

@Anonymous Try following measure, I break down the measures in smaller pieces to easily understand what is going on

Sum of Value = SUM ( 'Top'[Value] ) --get base measure to sum of value

--get sum of most recent date selected
Sum of Latest Date = 
VAR __latestDate = MAX ( 'Top'[Date] )
RETURN
CALCULATE (
    [Sum of Value],
    'Top'[Date] = __latestDate 
)

--get sum of top 5

Sum of Top 5 = 
VAR __top5 =   
TOPN ( 
    5, 
    ALLSELECTED( 'Top'[Company] ), 
    [Sum of Latest Date], 
    DESC 
) 
RETURN
CALCULATE (
    [Sum of Latest Date],
   KEEPFILTERS( 'Top'[Company] IN __top5  )
)

[Anonymous]

Thank you! The second measure is exactly what I needed!

[parry2k]

@Anonymous and here is the output if most recent selected date is Feb 28th

[parry2k]

@Anonymous missed to mention that you can use bar chart  instead of me showing the result in table visual

[Anonymous]

By the way, because I'm already using a top 5 filter to select the top 5 values, I can't add another "Top N" filter for the date. I think what I need is a measure to flag the latest selected date. 

Thanks!