DAYS360 05-02-2020 16:12 PM - last edited 05-10-2020 16:06 PM

[Greg_Deckler]

In my recent quest to create or catalog as many DAX equivalents for Excel functions, this one is for all the accountants out there that are so stodgy, old fashioned and resistant to change that they refuse to use any other calendar than the one used by the Babylonians and Mayans...

DAYS360 = 
    VAR __Date1 = MAX([Date1])
    VAR __Date2 = MAX([Date2])
    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

eyJrIjoiYzEzNzZiYjQtMGY2Ni00N2U1LWJjYjQtNDcwOTYwZjk1M2U4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Anonymous]

Hi, Greg_Deckler!

You, sir, are a lifesaver!  Been pounding my head on a wall for weeks on this one. 

Thank you!