How to switch between 2 calculations in one

[DLU]

Dear community, 

I'm currently struggling with a DAX calculation. I've created the following DAX measure: 

Total Trans QTY LTM =

VAR _Date = MAX('Date'[Date])

VAR _Startdate = DATE(YEAR(_Date)-1;MONTH(_Date);DAY(_Date))

VAR _BOMitem = IF(SELECTEDVALUE('ITEMID BOM'[BOM version.BOMID])="Blank";1;0)

VAR _Workorder = FIND(SELECTEDVALUE('INVENTTRANS OUT MERGED'[REFERENCEID]);"WO";1;0)

VAR _Table = FILTER('INVENTTRANS OUT MERGED';'INVENTTRANS OUT MERGED'[INVENTTRANS.DATEPHYSICAL]>=_Startdate)

Return

SUMX(_Table;'INVENTTRANS OUT MERGED'[INVENTTRANS.QTY]*-1)

But I want to change the return part. Just for some context, I'm trying to calculate the stock turnover, but for certain items that calculation needs to be different from others. Therefore I already wrote 2 variables to classify those requirements. What I want in the end is: 

When the BOM version. BOMID returns 1 I want to keep the same result as now presented, so INVENTTRANS.QTY *-1 on the filtered _Table. But when the BOM version.BOMID returns 0 (so not blank) I want the INVENTTRANS.QTY to be filtered, because I don't want to include the qty that has "WO" in the REFERENCEID. Therefore I already put in the requirement for identifying "WO", but after that I'm lost. I've tried to do it with an IF statement but because I'm trying to combine 2 calculations into 1, I don't have a necessary True or false outcome. It just needs to be one or the other. 

I really appreciate the help!

With kind regards, Diana

[v-jialluo-msft]

Hi  @DLU ,

Based on your description, your need is to filter the data by situation and make totals.

You can try this:

Total Trans QTY LTM =
VAR _Date = MAX('Date'[Date])
VAR _Startdate = DATE(YEAR(_Date)-1,MONTH(_Date),DAY(_Date))
VAR _BOMitem = IF(SELECTEDVALUE('ITEMID BOM'[BOM version.BOMID])="Blank",1,0)
VAR _Workorder = FIND(SELECTEDVALUE('INVENTTRANS OUT MERGED'[REFERENCEID]),"WO",1,0)
VAR _FLAG = IF (__BOMitem = 1,1,IF(__Workorder=1,0,1))
VAR _Table = FILTER('INVENTTRANS OUT MERGED','INVENTTRANS OUT MERGED'[INVENTTRANS.DATEPHYSICAL]>=_Startdate &&_FLAG=1)
Return
SUMX(_Table,'INVENTTRANS OUT MERGED'[INVENTTRANS.QTY]*-1)

If that's not what you need, provide sample data and projected output.

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DLU]

Dear Gallen, 

I've tried your suggestion but unfortunately it didn't give the right output, so like you requested hereby the sample data. 

First the item table in which the BOMid is registered. 

ITEMID	BOMID
dog	"Blank"
cat	B001

Then, the table in which the quantity is registered per referenceid.

ITEMID	REFERENCEID	QTY
dog	WO1	5
dog	WO2	5
dog	IC01	5
dog	IC02	5
dog	SO1	5
dog	SO2	5
cat	WO3	5
cat	WO4	5
cat	IC03	5
cat	IC04	5
cat	SO3	5
cat	SO4	5

And finally, the numbers PowerBI should come up with. 

ITEMID	QTY
dog	30
cat	20

So just to summarize, if the bomid isn't empty I want to include all the quantities, except the ones that contain "WO" in the REFERENCEID. If the bomid is empty, I want to include all the quantities, so also "WO". I didn't elaborate on the maxdate function, because that's already doing what it should do. 

Again, many thanks for you help!

Gr. Diana