COUPON 05-10-2020 16:56 PM - last edited 05-10-2020 17:02 PM

[Greg_Deckler]

In my recent quest to create or catalog as many DAX equivalents for Excel functions, these were incredibly frustrating just simply becuase of the rampant lack of documentation. I mean, wow, one might call it useless I suppose but I consider the term useless far too positive of a description. I literally had to reverse engineer these things. 

There are certain circumstances where my COUPDAYS does not match Excel's, there is a day or .5 or .25 days off when using a Basis of 3 but without some guidance as to what the flip Excel's functions are doing under the hood for an Actual/365; whatever that means, who knows what is going on. And that keen insight is certainly not coming from the garbage documentation.

The PBIX includes measures for the following Excel functions:

• COUPDAYS
• COUPDAYSNC
• COUPDAYBS
• COUPNCD
• COUPPCD
• COUPNUM

I am only posting here in the text a respresentative sample because they are all rather repetitive.

COUPNCD = 
    VAR __Settlement = [Settlement]
    VAR __Maturity = [Maturity]
    VAR __Frequency = MAX('Table'[Frequency])
    VAR __Basis = MAX('Table'[Basis])
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(0,4,1),
            "Date",
                VAR __Months = (4 - [Value]) * (-3)
                VAR __Date = EOMONTH(__Maturity,__Months)
                RETURN DATE(YEAR(__Date),MONTH(__Date),DAY(__Maturity))
        )
RETURN
    SWITCH(__Frequency,
        1,MINX(FILTER(__Table,([Value] = 0 || [Value] = 4) && [Date] > __Settlement),[Date]),
        2,MINX(FILTER(__Table,([Value] = 0 || [Value] = 2 || [Value] = 4) && [Date] > __Settlement),[Date]),
        4,MINX(FILTER(__Table,[Date] > __Settlement),[Date]),
        BLANK()
    )

COUPDAYSNC = 
    VAR __Settlement = [Settlement]
    VAR __Maturity = [Maturity]
    VAR __Frequency = MAX('Table'[Frequency])
    VAR __Basis = MAX('Table'[Basis])
    VAR __COUPNCD = [COUPNCD]
RETURN
    SWITCH(TRUE(),
        __Basis = 1 || __Basis = 2 || __Basis = 3, ( __COUPNCD - __Settlement ) * 1.,
        __Basis = 0 || __Basis = 4 || ISBLANK(__Basis),
            VAR __Date1 = __Settlement
            VAR __Date2 = __COUPNCD
            VAR __CalendarBase =
                FILTER(
                    CALENDAR(__Date1,__Date2),
                    DAY([Date]) <= 30
                )
            VAR __FebCal =
                SUMMARIZE(
                    ADDCOLUMNS(
                        FILTER(
                            __CalendarBase,
                            MONTH([Date]) = 2 && (DAY([Date]) = 28 || DAY([Date]) = 29) ||
                                MONTH([Date]) = 3 && DAY([Date]) = 1
                        ),
                        "Year",YEAR([Date])
                    ),
                    [Year],
                    "FebExtraDays",
                        VAR __Year = [Year]
                        VAR __Div4 = IF(MOD(__Year,4)=0,TRUE(),FALSE())
                        VAR __Div100 = IF(MOD(__Year,100)=0,TRUE(),FALSE())
                        VAR __Div400 = IF(MOD(__Year,400)=0,TRUE(),FALSE())
                        VAR __IsLeapYear = IF(__Div4 && NOT(__Div100),TRUE(),IF(__Div4 && __Div100 && __Div400,TRUE(),FALSE()))
                    RETURN IF(__IsLeapYear,1,2)
                )
            VAR __Adjustment = IF(DAY(__Date1)>30 || DAY(__Date2)>30,0,-1)                
        RETURN
            COUNTROWS(__CalendarBase) + SUMX(__FebCal,[FebExtraDays]) + __Adjustment,
        BLANK()
    )

NOTE: Creating these functions helped me identify a bug in DAYS360 so I updated that in the Quick Measures Gallery. Also, and I just have to rant on this because these took me pretty much all day to figure out when it should have been like an hour because, let's face it, these things are pretty brainless once you reverse engineer what they are doing, but I mean, not even the naming convention is consistent. You have COUPNCD (coupon next coupon date), COUPPCD (coupon previous coupon date), COUPDAYS, COUPDAYSNC and then..drum roll...COUPDAYBS, I mean, shouldn't it be COUPDAYSPC for; oh I don't know, freaking consistency sake if nothing else? Come on man...that's just lazy. I mean, it's really kinda BS or a developer TOTALLY slipped that one by management.

And, honestly, the Excel functions seems buggy to me because, for example, using the maturity and settlement and a Frequency of 1 and Basis of 2 Excel gives back 294 for COUPDAYSNC and 71 for COUPDAYBS but then COUPDAYS maxes out at 360. Well, using the 360 calculation for COUPDAYSNC and COUPDAYBS gives back 290 and 70 respectively. So... Anyway, it's all a trainwreck in my opinion.

eyJrIjoiODg3MmYwYjctZTk4ZC00YzY4LWFhMzItYjMzMDM3N2I1YzRlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9