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

gsbaker

The "Rlzd Cap Gain" measure does not appear to calculate the gain/loss on a "TransferOutAsSale" transaction. The "Rlzd Cap Gain w/o Sell Fee" measure seems to do this calculation. Is there some reason why Rlzd Cap Gain does not do the calculation for this transaction type? I don't know DAX, but it looks like the only difference in the DAX coding seems to be that the "w/o Sell Fee" measure includes "+ [Fee]" in the calculation.

Thanks

gsbaker

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

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

gsbaker

No worries Vidas. Thanks for looking into this.

VidasMatelis

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

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

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

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

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

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

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

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

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

Thanks