topic Calculate SUM for single value from multi-select slicer in DAX Commands and Tips

Calculate SUM for single value from multi-select slicer

Anonymous — Thu, 28 Apr 2022 10:11:14 GMT

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):

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:

How do I achieve that it only shows March now?

Thanks for your help.

Re: Calculate SUM for single value from multi-select slicer

amitchandak — Thu, 28 Apr 2022 11:00:04 GMT

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)

Re: Calculate SUM for single value from multi-select slicer

Anonymous — Thu, 28 Apr 2022 13:18:38 GMT

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!