Code Extender | DAX

DAX Business Days Calculation with First Day Exclusion

Extension modifies existing DAX code to exclude the first day of the period while calculating business days. Adjusts logic in FILTER function, retaining clarity in the naming and IF statement setup.


Empty image or helper icon

Prompt

BusinessDaysDifference = 
VAR StartDate = [date1]
VAR EndDate = [date2]
RETURN
IF(
    ISBLANK(StartDate) || ISBLANK(EndDate),
    BLANK(),
    COUNTROWS(
        FILTER(
            CALENDAR(StartDate, EndDate),
            WEEKDAY([Date], 2) < 6
        )
    )
)

Answer

Analysis of Existing Code

The existing code snippet is written in DAX (Data Analysis Expressions) language and calculates the number of business days between two given dates.

Extension Objective

Integrate the functionality to exclude the first day of the period from the business days calculation.

Extended Code (DAX)

BusinessDaysDifference_ExcludeFirstDay = 
VAR StartDate = [date1]
VAR EndDate = [date2]
RETURN
IF(
    ISBLANK(StartDate) || ISBLANK(EndDate),
    BLANK(),
    COUNTROWS(
        FILTER(
            CALENDAR(StartDate, EndDate),
            AND(
                WEEKDAY([Date], 2) < 6,
                [Date] <> StartDate
            )
        )
    )
)

Main Points

  1. Extension Logic: Added a condition in the FILTER function to exclude the StartDate from the count of business days.
  2. Naming Convention: Renamed the measure to clearly indicate the exclusion of the first day.
  3. IF Statement Modification: The StartDate is now excluded from the calculation by adding [Date] <> StartDate condition within the FILTER.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

Extension modifies existing DAX code to exclude the first day of the period while calculating business days. Adjusts logic in FILTER function, retaining clarity in the naming and IF statement setup.

More Code Extenders
Apache Flink Code ExtenderApache Pig Code ExtenderAzure Data Factory Code ExtenderC/C++ Code ExtenderCouchDB Code ExtenderDAX Code ExtenderExcel Code ExtenderFirebase Code ExtenderGoogle BigQuery Code ExtenderGoogle Sheets Code ExtenderGraphQL Code ExtenderHive Code ExtenderJava Code ExtenderJavaScript Code ExtenderJulia Code ExtenderLua Code ExtenderM (Power Query) Code ExtenderMATLAB Code ExtenderMongoDB Code ExtenderOracle Code ExtenderPostgreSQL Code ExtenderPower BI Code ExtenderPython Code ExtenderR Code ExtenderRedis Code ExtenderRegex Code ExtenderRuby Code ExtenderSAS Code ExtenderScala Code ExtenderShell Code ExtenderSPSS Code ExtenderSQL Code ExtenderSQLite Code ExtenderStata Code ExtenderTableau Code ExtenderVBA Code Extender