Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
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
Hi, Greg_Deckler!
You, sir, are a lifesaver! Been pounding my head on a wall for weeks on this one.
Thank you!