Excel to Dax, using sumifs formula

[nnouchi]

I'm in process of converting an excel report to Power BI. This includes matching the output of a column that has a SUMIFS formula included in the conditional statement.

The columns are exactly the same in both reports, but when creating the calculated column in Power BI, I'm finding it difficult to emulate what is done with the Excel formula

Excel formula:

=IF([@[Shipped/CSO]]="SHIPPED",0,IF([@SOType]="SO",SUMIFS([QTY],[InvoiceDate],"="&"",[Index],"<"&[@Index],[Branch],[@Branch],[Part],[@Part]),0))

Dax formula : 

IF([Shipped/CSO] = "SHIPPED",0,IF(Sales[SOType] = "SO",SUMX(FILTER(Sales,Sales[InvoiceDate]=EARLIER(Sales[InvoiceDate],1)&& Sales[Branch]=EARLIER(Sales[Branch],1) && Sales[Part] = EARLIER(Sales[Part],1)),Sales[QTY]),0))

I am willing to provide an example of the table information, but the Sales table has exactly the same information.

Thanks for the help!

[harshnathani · Accepted solution]

Hi @nnouchi ,

Try this Calculated Column

Column =
SWITCH(
TRUE(),

'Sales'[Shipped/CSO] = "Shipped", 0,

SUMX(FILTER(Sales,Sales[InvoiceDate]=EARLIER(Sales[InvoiceDate])&& Sales[Branch]=EARLIER(Sales[Branch]) && Sales[Part] = EARLIER(Sales[Part]),Sales[QTY]))

)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[nnouchi]

Harsh,

The result is the same, which is making me question whether the difference between indexes is causing the difference in output. 

I believe this is the correct answer, so I will be accepting this for the thread.

Thank you for your efforts.

Nicolas