skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • DAYS360

    DAYS360

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

    Super User Greg_Deckler
    Super User
    840 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    DAYS360

    ‎05-02-2020 04:12 PM

    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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Preview file
    20 KB
    DAYS360.pbix
    Labels:
    • Labels:
    • Financial
    • Mathematical
    • Other
    • Time Intelligence
    Message 1 of 2
    840 Views
    1
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    GaryBurnore
    GaryBurnore
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-04-2022 12:44 PM

    Hi, Greg_Deckler!

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

    Thank you! 

    Message 2 of 2
    253 Views
    2
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices