cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Bar chart based on the latest date of a slicer

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

1 ACCEPTED SOLUTION
Super User

@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  )
)
``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

6 REPLIES 6
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@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  )
)
``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.