Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Calculate SUM for single value from multi-select slicer

Hey guys,


hope you can help me out with this one.

I have a filter on month names (for simplicity let's imply the year is always 2022): 

SeekingHelp_0-1651139821362.png

And another slicer that allows period selection (MTD/Full). I'm already calculating the sum over the selected period.
Now, what I want to achieve is, that it is only calculating the sum 
and displaying it in a bar chart for the most recent month selected (e.g. March) when the user has MTD selected.


I did the calculation like that: 

CALCULATE (
SUM ( value ),
...
Dates[Year Month Number] = MAX ( Dates[Year Month Number] )
)

That works fine and I'm also able to show the whole sum over all months when the user selects "Full" in the other slicer.
Here is the problem: 
In a bar chart, Max ( Dates[Year Month Number] ) will always refer to the month the bar shows, so it displays all the selected months instead only the most recent one:
SeekingHelp_1-1651140395238.png

 


How do I achieve that it only shows March now?
 
Thanks for your help.
 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Create a measure

 

measure =

var _max = eomonth(maxx(allselected(Dates) , Dates[Date]) ,0)

return

calculate(sum(Table[Value]), filter(Dates, eomonth(Dates[Date],0) =_max)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Create a measure

 

measure =

var _max = eomonth(maxx(allselected(Dates) , Dates[Date]) ,0)

return

calculate(sum(Table[Value]), filter(Dates, eomonth(Dates[Date],0) =_max)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak 

Thanks for your reply. Solved my issue with the MTD calculation. But I'm not able to transfer your example to a YTD calculation. If a user selects YTD, I want to show all months of the most recent year in a bar chart, despite the fact that the user might have not selected all months. How do I deal with this?


Appreciate your help!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.