Formula Help (SUMIF not available)

[stokesmcr]

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!!

[stokesmcr · Accepted solution]

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

[GilesWalker]

@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

[stokesmcr]

@GilesWalker

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

[matemusic]

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