Answered by:
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"))
ThanksThursday, July 21, 2016 5:11 PM
Answers
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 :)
 Edited by geraldartman Friday, July 22, 2016 4:16 PM
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 201501,
201502?
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 

Great!
That works, thanks
Monday, July 25, 2016 1:28 PM 