Help with SWAP Curve charts

DAX DAX Calculations
1

Hi everybody I hope you are all well,

My latest post is seeking some help with SWAP curves. Attached is my excel feed from Bloomberg. There is a lot of tabs but what the focus on for me here is the 30yr to O_N SWAP tabs.

When I load this data into PowerBI I append the queries and tag each one with its identifier, i.e. 10 year SWAP, 3 Year SWAP etc. So that I end up with a table like this in BI

image
image425×699 35.2 KB

What I want to do is use the “identifier” column as the X Axis and the date as legend so that I can compare different SWAP curves at different points in time.

For Today’s Curve I just put in the following DAX = SWAP Curve Today = CALCULATE([SWAP Rate],
filter(ALLSELECTED(Dates[Date]),
Dates[Date] = max(Dates[Date])))

This is where the SWAP Rate = SWAP Rate = sum(‘US10 Year Swap’[PX_BID]).

This all works perfectly for today’s curve but the issue I have is trying to set specific dates to compare. I thought a dateadd would work but I am struggling to get it to work as I am looking for values at a date in time at different points of the curve.

I used the following SWAP Curve 1 week ago = CALCULATE([SWAP Rate],
filter(ALLSELECTED(Dates[Date]),
Dates[Date] = max(Dates[Date])-90)) to do a test and go 90 days back and this works but it is a bad way to write the formula, it will not account for weekends etc.

I also tried to create date measure for example Date last year Date last year = dateadd(‘LastDate for Data’[LastDate for Data], -1, YEAR) and then I was trying to use this measure to drop into my standard formula to get the value at this date last year.
SWAP Curve last year = CALCULATE([SWAP Rate],
filter(ALLSELECTED(Dates[Date]),
Dates[Date] = ‘Date Measures’[Date last year]))

But this D
Financial Market Report.pbix (5.0 MB)
Financial Market Data.xlsx (9.1 MB)
ax returns a nothing value.

If anyone was able to help with this it would be greatly appreciated. The workings on charts are in tab “Page 1” of the attached BI report.

Thanks all

2

Hi @Ronan - Firstly, Legend is not allowed if there are multiple values in a Visual. Second, I don’t see any issues in your formula and it seems to be working correctly.

Can try below but mostly it is similar. If issue is something else, then do explain.

Test SWAP Curve 1 week ago = 
var MaxDateMinus7 = CALCULATE(max(Dates[Date]),ALLSELECTED(Dates)) - 7
return
CALCULATE([SWAP Rate],Dates[Date] = MaxDateMinus7)

Thanks
Ankit J

3

Hi @Ronan

Are you looking for something like this:

image
image1730×980 91.3 KB

Set up line chart

image
image382×605 20.1 KB

and two slicers filter: Year and Security (Category->Rate on filter panel)

1 Like
4

Thanks @jbressan but I was looking for the chart to be switched with the legend being the axis and the value being that item at a date.

5

Thank you, I just have to go with this approach even though sometimes I will get weekends etc. thanks again

6

@Ronan,

If you are using @Melissa 's awesome Extended Date Table, it’s a straightforward matter to filter out weekend days by setting Day Type = “Weekday” either in your DAX expression or in the filter pane.

  • Brian
7

Thanks Brian,

Yes I am using @Melissa amazing Date Table so for example in this formula
CALCULATE(max(Dates[Date]),ALLSELECTED(Dates)) - 7

Where would you include the Day Type just as another parameter filter?

8

@Ronan,

Give this a go:

CALCULATE (
    MAX ( Dates[Date] ),
    ALLSELECTED ( Dates ),
	Dates[Day Type] = "Weekday"
) - 7
9

Thank you for this, greatly appreciate it

2 Likes