Forum Discussion
Excel to Dax, using sumifs formula
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!
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)
2 Replies
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)
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
