How to Remove a monthly filter and apply a yearly filter

[Anonymous]

Hi,

I need to compare Bonuses (reported yearly ) and annual salaries (reported monthly. The amounts vary each month due to employees changes.).

The way things are, I have to use a slicer of "Year-Month" to choose Dec-16 or Dec-17.

I need the Bonus measure to aggregate the whole corresponding year (ie 2016 or 2018).

How should I compose my DAX formula?

Upon selecting Dec-16 from the "Month-Year" Slicer, I want the Salaries to show just that (which it does) and I want all(!) the Bonuses are given on the year to be aggregated. 

I created the measure with the hard-coded year 2016 for example but even that gives me an error:

TotBonusLocAllYear = SUMX(FILTER(ALL(dimCalendar[MonthYear]), YEAR(factsBonuses[Effective Date])=2016),[TotBonusLoc] )

Thanks,

Tamir

[v-jiascu-msft]

Hi Tamir,

You can use function All to clear the filter from monthly slicer. The formula could be like this:

TotBonusLocAllYear =
CALCULATE (
    SUM ( factsBonuses[Bonuses] ),
    FILTER ( factsBonuses, factsBonuses[Effective Date] = 2016 ),
    ALL ( dimCalendar[MonthYear] )
)

Or this:

TotBonusLocAllYear =
CALCULATE ( SUM ( factsBonuses[Bonuses] ), ALL ( dimCalendar[MonthYear] ) )

If you want more accurate formula, please provide a dummy sample.

Best Regards,

Dale

[Anonymous]

Thank you Dale,

In the suggested DAX, how can I instruct the Measure to apply the Year of the selected month (from the slicer)?

TotBonusLocAllYear =
CALCULATE ( SUM ( factsBonuses[Bonuses] ), ALL ( dimCalendar[MonthYear] ) )

Meaning, I have a Month-Year Slicer, When I click on Dec-2016 or Sep-2016 I want the value 2016 go into the DAX. 

I also tried: 

TotBonusLocAllYear3 = CALCULATE(
                            SUM(factsBonuses[Payment Amount]), 
                            ALL(dimCalendar[MonthYear]), 
                            FILTER(dimCalendar,dimCalendar[Year]=ENDOFYEAR(dimCalendar[Date]))
                            )

Which returns blank!

Thank you,

Tamir