Code Extender | DAX

DAX Code Enhancement for Business Days

The DAX code snippet introduces functionality to display 0 instead of a blank when the result of COUNTROWS is 0. It adds a DayCount variable and nested IF statement to refine business days calculation seamlessly.


Empty image or helper icon

Prompt

BusinessDaysDifference = 
VAR StartDate = [Checked at input LP date]
VAR EndDate = [Delivery_date (by place)]
RETURN
IF(
    ISBLANK(StartDate) || ISBLANK(EndDate), -- neskaičiuojame rodiklio, jei starto ir pabaigos datos yra tuščios
    BLANK(),
    COUNTROWS(
        FILTER(
            CALENDAR(StartDate, EndDate), -- susiformuojame kalendorių tarp starto ir pabaigos datų
            AND(
                WEEKDAY([Date], 2) < 6, -- traukiame tik darbo dienas be savaitgalių
                [Date] <> StartDate  -- netraukiame starto dienos
            )
        )
    )
)

Answer

Extended Code in DAX Language

In the existing code snippet, an additional functionality is required to display 0 instead of a blank when the result of COUNTROWS is 0. Below is the extended version of the DAX code incorporating this feature:

BusinessDaysDifference = 
VAR StartDate = [Checked at input LP date]
VAR EndDate = [Delivery_date (by place)]
VAR DayCount = 
    COUNTROWS(
        FILTER(
            CALENDAR(StartDate, EndDate), 
            AND(
                WEEKDAY([Date], 2) < 6,
                [Date] <> StartDate
            )
        )
    )
RETURN
IF(
    ISBLANK(StartDate) || ISBLANK(EndDate), 
    BLANK(),
    IF(DayCount = 0, 0, DayCount)
)

Key Points

  • Introduced a new variable DayCount to store the count of business days between StartDate and EndDate.
  • Utilized an additional IF statement inside the main IF condition to check if DayCount is 0 and display 0 instead of a blank result.
  • Ensured the seamless integration of this new functionality without altering the original code's purpose or flow.

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

The DAX code snippet introduces functionality to display 0 instead of a blank when the result of COUNTROWS is 0. It adds a DayCount variable and nested IF statement to refine business days calculation seamlessly.

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