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 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 -
-
Great!
That works, thanks
Monday, July 25, 2016 1:28 PM -