locked
DAX-Formula to calculate total sum of year and month-filter RRS feed

  • 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

Answers

  • 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 chmav Wednesday, January 21, 2015 8:58 PM
    Wednesday, January 21, 2015 1:58 PM
    Answerer

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 chmav Wednesday, January 21, 2015 8:58 PM
    Wednesday, January 21, 2015 1:58 PM
    Answerer
  • 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
  • how about this one?

    Sales_FullYear2:=CALCULATE([SumSales], ALL('Date'), VALUES('Date'[Calendar Year]))

    -gerhard


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

    Thursday, January 22, 2015 8:43 AM
    Answerer