Issue with IFERROR Not Catching Errors Row by Row When Using YIELD in Power BI

ElenaStone — Thu, 01 Aug 2024 15:34:37 GMT

Hello,

I'm experiencing an issue with the YIELD function in Power BI. I'm trying to calculate the yield, but it seems that IFERROR is not catching errors on a row-by-row basis and assigning values when an error is detected.

Here are snapshots of my Excel and Power BI setup:

Excel Snapshot:

Power BI Snapshot:

The formula I'm using in Power BI is:

YIELD = YIELD(test[SETTLEMENT_DATE], test[MATURITY], test[COUPON], test[LAST_PRICE], test[PAR], test[Freq])

All columns seem to have the correct data types, but I still encounter #ERROR values. In Excel, I get #NUM! and some valid yield values as expected. However, in Power BI, I was expecting it to show results row-by-row and leave blank if there's an error, instead of showing an error for the whole column.

I tried wrapping the YIELD function with IFERROR, but it doesn't seem to handle the errors as expected. Is there any way to make Power BI show the result row-by-row and leave the cell blank if it encounters an error?

Attached the data here for reference

SETTLEMENT_DATE	MATURITY	COUPON	LAST_PRICE	PAR	Freq
07/30/2024	07/31/2024	11.00%	9	100	4
07/30/2024	08/31/2024	9.40%	94	100	4
07/30/2024	08/31/2024	9.40%	95	100	4

Thank you!

Re: Issue with IFERROR Not Catching Errors Row by Row When Using YIELD in Power BI

BeaBF — Thu, 01 Aug 2024 16:09:47 GMT

ElenaStone the formula does not recognizes that Settlement_Date is previous than Maturity, infact, with this code, the column returns the correct output:

Test_Yield =

var validsettle = IF(ISERROR(DATEVALUE(test[SETTLEMENT_DATE])), BLANK(), DATEVALUE(test[SETTLEMENT_DATE]))

var validmaturity = IF(ISERROR(DATEVALUE(test[MATURITY])), BLANK(), DATEVALUE(test[MATURITY]))

RETURN

YIELD(

DATE(YEAR(validsettle), MONTH(validsettle), 01),

validmaturity,

test[COUPON], test[LAST_PRICE], test[PAR], test[Freq])

i've put 01 instead of 30 as day of settlement_date.

We'll retain the dynamic nature of SETTLEMENT_DATE but handle its day value to avoid errors:

YIELD_WITH_ERROR_HANDLING =

VAR ValidSettlementDate = IF(ISERROR(DATEVALUE(test[SETTLEMENT_DATE])), BLANK(), DATEVALUE(test[SETTLEMENT_DATE]))

VAR ValidMaturityDate = IF(ISERROR(DATEVALUE(test[MATURITY])), BLANK(), DATEVALUE(test[MATURITY]))

VAR AdjustedSettlementDate = DATE(YEAR(ValidSettlementDate), MONTH(ValidSettlementDate), 1) -- Adjust day to 1

VAR ValidCoupon = IF(ISERROR(VALUE(test[COUPON])), BLANK(), VALUE(test[COUPON]))

VAR ValidLastPrice = IF(ISERROR(VALUE(test[LAST_PRICE])), BLANK(), VALUE(test[LAST_PRICE]))

VAR ValidPar = IF(ISERROR(VALUE(test[PAR])), BLANK(), VALUE(test[PAR]))

VAR ValidFreq = IF(ISERROR(VALUE(test[Freq])), BLANK(), VALUE(test[Freq]))

RETURN

IF(

ISBLANK(ValidSettlementDate) ||

ISBLANK(ValidMaturityDate) ||

ISBLANK(ValidCoupon) ||

ISBLANK(ValidLastPrice) ||

ISBLANK(ValidPar) ||

ISBLANK(ValidFreq),

BLANK(),

IFERROR(

YIELD(

AdjustedSettlementDate,

ValidMaturityDate,

ValidCoupon,

ValidLastPrice,

ValidPar,

ValidFreq

BLANK()

)

BBF

topic Issue with IFERROR Not Catching Errors Row by Row When Using YIELD in Power BI in DAX Commands and Tips

Issue with IFERROR Not Catching Errors Row by Row When Using YIELD in Power BI

Re: Issue with IFERROR Not Catching Errors Row by Row When Using YIELD in Power BI