locked
SumIf in PowerPivot 2013 RRS feed

  • Question

  • Trying to do a simple sumif in PowerPivot and I keep getting an #error message.

    Can anyone help?

    Calculated Field 2:=CALCULATE(SUM(Sales[NetSales],FILTER(Sales[Year]= 2015)),FILTER(Sales[MonthNo]= "01"))

    Thanks
    Thursday, July 21, 2016 5:11 PM

Answers

  • Hi DeniseDD,

    Please try the expression below.

    Copy of PYTDTotal:=CALCULATE(SUM(Sales[NetSales]),Sales[YrMth]<= (YEAR(now())-1 &"-"&RIGHT("00"&month(now()),2))) 
    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by DeniseDDD Tuesday, July 26, 2016 1:17 AM
    Monday, July 25, 2016 6:07 AM

All replies

  • Calculate Field 2:= CALCULATE(SUM(Sales[NetSales]), FILTER(Sales, Sales[Year]=2015 && Sales[Month]="01"))

    No closing ) for SUM. Extra ) after first filter.

    You can combined the two conditions into one filter. && for AND, || for OR. There also is and AND() and OR() verb with comma separated fragments returning true/false.

    It is easy to forget that DAX doesn't autocomplete like Excel :-)


    Friday, July 22, 2016 4:15 PM
  • Thank you for the response, I changed my formula to the below, but I am still getting the #error message.

    Could the issue be that the month field is a calculated column for another field where the format is 2015-01,

    2015-02?

    Calculate Field 2:=CALCULATE(SUM(Sales[NetSales]), FILTER(Sales, Sales[Year]=2015 && Sales[Mth]="01"))

    What I am trying to accomplish is sum all pervious sales though the current month.

    I was using this formula but it will not work after sept, since I will no longer need the leading zero

    Copy of PYTDTotal:=CALCULATE(SUM(Sales[NetSales]),Sales[YrMth]<= (YEAR(now())-1 &"-"&"0"& month(now())))

    Thanks again

    Friday, July 22, 2016 6:11 PM
  • Hi DeniseDD,

    Please try the expression below.

    Copy of PYTDTotal:=CALCULATE(SUM(Sales[NetSales]),Sales[YrMth]<= (YEAR(now())-1 &"-"&RIGHT("00"&month(now()),2))) 
    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by DeniseDDD Tuesday, July 26, 2016 1:17 AM
    Monday, July 25, 2016 6:07 AM
  • Great!

    That works, thanks

    Monday, July 25, 2016 1:28 PM
  • Great!

    That works, thanks

    Hi Denise,

    Glad to hear that your issue got solved. Please mark it as answer which will help other to find the solution easily.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, July 26, 2016 1:16 AM