Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would like to make a coupon calendar for bond payments in power BI. Below is an example of the data I'm working with, and what kind of visual I would like to create (clustered column, with monthly buckets).
CP/FI | Issuer | Amount | Coupon | Maturity |
FI | GOOG | $ 1,000,000 | 2.63% | 2/15/2023 |
FI | APPL | $ 5,000,000 | 1.13% | 5/1/2023 |
FI | APPl | $ 5,000,000 | 2.70% | 5/18/2023 |
FI | X | $ 2,000,000 | 2.00% | 8/8/2023 |
FI | TGT | $ 5,000,000 | 2.40% | 9/15/2023 |
FI | VZ | $ 10,000,000 | 0.75% | 3/22/2024 |
FI | WMT | $ 2,000,000 | 2.85% | 7/8/2024 |
FI | WMT | $ 4,900,000 | 2.85% | 7/8/2024 |
FI | BAC | $ 1,000,000 | 4.20% | 8/26/2024 |
FI | GS | $ 3,000,000 | 4.00% | 1/22/2025 |
FI | BAC | $ 5,642,000 | 3.95% | 4/21/2025 |
FI | PM | $ 2,000,000 | 1.50% | 5/1/2025 |
FI | MSFT | $ 4,170,000 | 2.65% | 11/3/2022 |
Thank you
Solved! Go to Solution.
@Anonymous,
I added a FILTER function to BondCoupon to exclude past maturity dates. Let me know if this resolves the issue.
BondCouponCalendar =
VAR vToday =
TODAY ()
VAR vTable =
GENERATE (
FILTER ( BondCoupon, BondCoupon[Maturity] >= vToday ),
VAR vMaturityDate = BondCoupon[Maturity]
VAR vMaturityMonthOffset =
MONTH ( EDATE ( vMaturityDate, -6 ) )
VAR vDates =
CALENDAR ( vToday, vMaturityDate )
VAR vPaymentDates =
FILTER (
vDates,
DAY ( [Date] ) = DAY ( vMaturityDate )
&& (
MONTH ( [Date] ) = MONTH ( vMaturityDate )
|| MONTH ( [Date] ) = vMaturityMonthOffset
)
)
RETURN
vPaymentDates
)
VAR vResult =
ADDCOLUMNS (
vTable,
"Coupon Payment", BondCoupon[Amount] * DIVIDE ( BondCoupon[Coupon], 2 )
)
RETURN
vResult
Proud to be a Super User!
@DataInsights I have used this calendar for some time now succesfuly and wanted to add some functionality to it.
Right now it creates a table for everything that matures in the future, but I was hoping to add a slicer to look back in time at things that have already matured. Instead of VAR vToday =TODAY() I have tried putting the following into it:
Thanks in advance
@Anonymous,
Glad to hear it's been working successfully. Calculated tables and calculated columns don't recognize user-specified filters in Report view. Thus, when you select -3 days in the slider, it doesn't have any impact on the calculated table. Hopefully, this feature will become available soon.
Proud to be a Super User!
@Anonymous,
This solution uses the GENERATE function to create a calculated table. The BondCoupon table is your sample data. The concept is to create a virtual date table that contains the payment dates for each bond. In the TGT example, three rows are generated since three more payments are expected. Let me know if leap year logic is required (i.e. a maturity date of February 29 in a leap year).
BondCouponCalendar =
VAR vToday =
TODAY ()
VAR vTable =
GENERATE (
BondCoupon,
VAR vMaturityDate = BondCoupon[Maturity]
VAR vMaturityMonthOffset =
MONTH ( EDATE ( vMaturityDate, -6 ) )
VAR vDates =
CALENDAR ( vToday, vMaturityDate )
VAR vPaymentDates =
FILTER (
vDates,
DAY ( [Date] ) = DAY ( vMaturityDate )
&& (
MONTH ( [Date] ) = MONTH ( vMaturityDate )
|| MONTH ( [Date] ) = vMaturityMonthOffset
)
)
RETURN
vPaymentDates
)
VAR vResult =
ADDCOLUMNS (
vTable,
"Coupon Payment", BondCoupon[Amount] * DIVIDE ( BondCoupon[Coupon], 2 )
)
RETURN
vResult
Proud to be a Super User!
First off, Thank you for this, this is kind of lbowing my mind. there is a lot in here that I have to learn.
I entered this and adjusted it for the actual table names and got an error "The start date in Calendar function cannot be later than the end date"
I am assuming this is because there are entries in my data that have already matured but do not get removed from the data set. So there are some with maturity dates in the past. Is there a way to modify this to only evaluate future maturity dates?
@Anonymous,
I added a FILTER function to BondCoupon to exclude past maturity dates. Let me know if this resolves the issue.
BondCouponCalendar =
VAR vToday =
TODAY ()
VAR vTable =
GENERATE (
FILTER ( BondCoupon, BondCoupon[Maturity] >= vToday ),
VAR vMaturityDate = BondCoupon[Maturity]
VAR vMaturityMonthOffset =
MONTH ( EDATE ( vMaturityDate, -6 ) )
VAR vDates =
CALENDAR ( vToday, vMaturityDate )
VAR vPaymentDates =
FILTER (
vDates,
DAY ( [Date] ) = DAY ( vMaturityDate )
&& (
MONTH ( [Date] ) = MONTH ( vMaturityDate )
|| MONTH ( [Date] ) = vMaturityMonthOffset
)
)
RETURN
vPaymentDates
)
VAR vResult =
ADDCOLUMNS (
vTable,
"Coupon Payment", BondCoupon[Amount] * DIVIDE ( BondCoupon[Coupon], 2 )
)
RETURN
vResult
Proud to be a Super User!
So far this does what I need it to do, thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |