# DAX-Formula to calculate total sum of year and month-filter

• ### Question

• Hi

In an Power Pivot-Excel report, the user can filter the month/Year(period) with a slicer. Then, he can see the turnover of the productgroups of this month. As last column, the user should see the turnover of this productgroups of the complete selected year.
The period-slicer has the values from a DimPeriod where each period 2015001, 2015002 has a column with the year of this period.
How can I select all the dates of the selected year as filter in ma Calcuate(SUM formula?

Best regards

Christoph

Tuesday, January 20, 2015 8:52 AM

• this should basically work:

Sales_FullYear:=CALCULATE([SumSales], ALLEXCEPT('Date', 'Date'[Calendar Year]))

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Marked as answer by Wednesday, January 21, 2015 8:58 PM
Wednesday, January 21, 2015 1:58 PM

### All replies

• this should basically work:

Sales_FullYear:=CALCULATE([SumSales], ALLEXCEPT('Date', 'Date'[Calendar Year]))

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Marked as answer by Wednesday, January 21, 2015 8:58 PM
Wednesday, January 21, 2015 1:58 PM
• Hi Gerhard

Thanks, that help me to solve this problem.

Christoph

Wednesday, January 21, 2015 9:02 PM
• Hi Gerhard
I done more tests and have a new queistion.
When I have one slicer to CalenderYear and one to Period(Jan 2014,...) (both from DimDate), and I select only one period, the Sales_FullYear calculates the sum of all years. When I select also one year, it's ok.
Is there a solution, where I need only the period-slicer and with the selected period, the data is filter by the year of this period?
So, I filter to "Jan 2014" and the Sales_FullYear is over 2014. When I filter "Feb 2013", the sum is over 2013 calculated.

Best regards

Christoph

Thursday, January 22, 2015 6:47 AM