SumIf in PowerPivot 2013

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Great!

That works, thanks

Answer 5

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