Rlzd Cap Gain measure not calculating Gain on "TransferOutAsSale" transaction

Comments

• gsbaker

  January 17 edited January 17

  I just noticed that the "Rlzd Cap Gain" measure also includes "TransType[TransTypeGroup] <> "Transfer"" later in the code, which likely filters out "transfer" type transactions. Although I would think that "TransferOutAsSale" should be included in the Capital Gain calculation. Am I missing something else?
• VidasMatelis

  January 17

  Sorry, Due to my schedule, it will take me at least 7days to investigate tsis
• gsbaker

  January 17

  No worries Vidas. Thanks for looking into this.
• VidasMatelis

  January 24

  Can you please try following updated formulas:

  Rlzd Cap Gain:=ROUND(
  CALCULATE(
   SUMX(FILTER(Transactions, NOT(IsBlank(Transactions[TransID])))
      , [TotalAmnt] * [TransExchRate] + 
        var currID = IF(HASONEVALUE(ReportCurrency[CurrencyID]), VALUES(ReportCurrency[CurrencyID]), 0)
        RETURN SWITCH(currID, 0, [CostBasisImpact], 1, [CostBasisImpactRpt1], 2, [CostBasisImpactRpt2], 3, [CostBasisImpactRpt3], [CostBasisImpact])
   )
   , TransType[BookValueSign] = -1, NOT(TransType[TransType] IN {"SymbolTransferIn","SymbolTransferOut"})
  ), 2)

  Rlzd Cap Gain w/o Sell Fee:=ROUND(
  CALCULATE(
   SUMX(FILTER(Transactions, NOT(IsBlank(Transactions[TransID])))
      , ([TotalAmnt] + [Fee]) * [TransExchRate] + 
        var currID = IF(HASONEVALUE(ReportCurrency[CurrencyID]), VALUES(ReportCurrency[CurrencyID]), 0)
        RETURN SWITCH(currID, 0, [CostBasisImpact], 1, [CostBasisImpactRpt1], 2, [CostBasisImpactRpt2], 3, [CostBasisImpactRpt3], [CostBasisImpact])
   )
   , TransType[BookValueSign] = -1, NOT(TransType[TransType] IN {"SymbolTransferIn","SymbolTransferOut"})
  ), 2)
  
• gsbaker

  February 2

  I tried the change on the "Rlzd Cap Gain" measure (my pivot tables do not use the "Rlzd Cap Gain w/o Sell Fee" measure so I didn't test that). While it is only a "test of one", it did seem to calculate the capital gain correctly for the TransferOutAsSale. However, there was another (perhaps) unintended consequence. The Sales table added ReturnOfCapital transactions as a "sale", even though those transaction types were filtered out. The report shows Qty, Sales Amount and Cost Basis as blanks, and Capital Gain as $0. Those ReturnOfCapital transactions did not previously appear on the Sales report.
  
  I did the test on my heavily modified model based on PS v3.0. Tomorrow, will run your original model using my data sets and let you know if there is any difference from my notes above.
• gsbaker

  February 2

  I ran my data sets using the original PS 3.0 Reports spreadsheet. Before updating the "Rlzd Cap Gain" measure the Sales table looks like the following (I added the "Rlzd Cap Gain" measure to the pivot table, and redacted some of the data for privacy):
  
  
  The "Rlzd Cap Gain" measure is blank, although the "Rlzd Cap Gain w/o Sell Fee" measure appeared to calculate correctly.
  
  After changing the "Rlzd Cap Gain" measure as suggested above, the portion of table with the "TransferOutAsSale" transaction now looks like this:
  
  
• VidasMatelis

  February 4

  Thanks for reporting this.
  Could you please try updated formula:

  Rlzd Cap Gain w/o Sell Fee:=IF([Qty Sold] <> 0,
  ROUND(
  CALCULATE(
   SUMX(FILTER(Transactions, NOT(IsBlank(Transactions[TransID])))
      , ([TotalAmnt] + [Fee]) * [TransExchRate] + 
        var currID = IF(HASONEVALUE(ReportCurrency[CurrencyID]), VALUES(ReportCurrency[CurrencyID]), 0)
        RETURN SWITCH(currID, 0, [CostBasisImpact], 1, [CostBasisImpactRpt1], 2, [CostBasisImpactRpt2], 3, [CostBasisImpactRpt3], [CostBasisImpact])
   )
   , TransType[BookValueSign] = -1, NOT(TransType[TransType] IN {"SymbolTransferIn","SymbolTransferOut"})
  ), 2)
  )
  
• gsbaker

  February 4

  That appears to have worked. I noticed that the above code was for the "Rlzd Cap Gain w/o Sell Fee" measure and that the revision was to wrap the previous change inside of the IF([Qty Sold] <>0, ... ) statement. So I applied this change also to the "Rlzd Cap Gain" measure. With those changes made to both measures, the report now seems to be working correctly.
  
  Thanks for your help.
• gsbaker

  February 4

  Perhaps a silly question, but I just noticed that the Grand Total line does not include totals for either "Rlzd Cap Gain" or "Rlzd Cap Gain w/o Sales Fee". Why is that?
• VidasMatelis

  February 5

  Can you please test this updated measure:

  Rlzd Cap Gain w/o Sell Fee:=IF([Sales Amnt] <> 0,
  ROUND(
  CALCULATE(
   SUMX(FILTER(Transactions, NOT(IsBlank(Transactions[TransID])))
      , ([TotalAmnt] + [Fee]) * [TransExchRate] + 
        var currID = IF(HASONEVALUE(ReportCurrency[CurrencyID]), VALUES(ReportCurrency[CurrencyID]), 0)
        RETURN SWITCH(currID, 0, [CostBasisImpact], 1, [CostBasisImpactRpt1], 2, [CostBasisImpactRpt2], 3, [CostBasisImpactRpt3], [CostBasisImpact])
   )
   , TransType[BookValueSign] = -1, NOT(TransType[TransType] IN {"SymbolTransferIn","SymbolTransferOut"})
  ), 2)
  )

  With this code I am checking if [Sales Amnt] is not 0, instead of [Qty Sold].
  
• gsbaker

  February 5 edited February 5

  That did it. Now calculates totals. Thanks.
  
  I am not that familiar with DAX, but am curious as to why this change impacted whether the field totals or not. Both are numerical fields.
• VidasMatelis

  February 6

  Measure [Qty Sold] formula is:

  Qty Sold:=IF(HASONEVALUE(Symbol[Symbol]), CALCULATE(SUM(Transactions[QtyChange]), TransType[SellFlag]=1))*(-1)
  

  So condition "IF(HASONEVALUE(Symbol[Symbol]),..." ensures that value is non empty just when a SINGLE symbol is selected. Because of this, value is empty for Grand Total when more than one symbol has sales. It is just additional logic specific to my code. Measure [Sales Amnt] does not check number of Symbols sold, so it is better candidate for using in this calculation, but it is a little bit slower.
• gsbaker

  February 6

  Thanks