Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to switch between 2 calculations in one

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
Need Help

2 Replies

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi  Anonymous ,

     

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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. 

      ITEMIDBOMID
      dog"Blank"
      catB001

       

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

       

      ITEMIDREFERENCEIDQTY
      dogWO15
      dogWO25
      dogIC015
      dogIC025
      dogSO15
      dogSO25
      catWO35
      catWO45
      catIC035
      catIC045
      catSO35
      catSO45

       

      And finally, the numbers PowerBI should come up with. 

      ITEMIDQTY
      dog30
      cat20

       

      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