Forum Discussion
Formula Help (SUMIF not available)
Hi All,
Need some Power BI formula geniuses.
I have a formula which I have created in Excel and trying to replicate in Power BI, but unable due to SUMIF function not available.
General Gist of Formula.
1) Current Time Period is May.
2) I want to add - what I have invoiced for the last period + what I have orders for next period (see green)
3) See picture below how I currently have this executed in Excel, I cannot implement in Power BI.
Data below is dummy for sake of the exercise but I have a much larger data set in the background.
Please help!!
Managed to resolve it.
Calculation = (CALCULATE(SUMX('Table'[Orders]),FILTER(Table,Table[Period]<>"APR 2016")) + (CALCULATE(SUMX('Table','Table'[Invoiced]),FILTER(Table,Table[Period]="APR 2016"))))
4 Replies
DAX formulas, which we use in Power BI have a little different logic. In your example, a think the best way is to use SUMX with FILTER function
SUMX((FILTER(<the name of your table>,"APR 2016"=<name column you want to compare>),<name of column you want to sum>)
stokesmcr - had this same issue the other day. This worked for me:
CALCULATE(SUMX('Table','Table'[Invoiced goods]+[Ordered goods]),FILTER(ALL('Table'),'Table'[Period]<=MAX('Table'[Period])))
Thanks should work for you. 'Table' is what ever your table/file name is.
Thanks,
Giles
Thanks for your help so far. How did you pass the time period requirement into the formula?
In my case - I want "Invoiced" for April and "Orders" for May + June.
Thank you, Michael
Managed to resolve it.
Calculation = (CALCULATE(SUMX('Table'[Orders]),FILTER(Table,Table[Period]<>"APR 2016")) + (CALCULATE(SUMX('Table','Table'[Invoiced]),FILTER(Table,Table[Period]="APR 2016"))))
