Logic Visualizer | Power BI

Paid Vacation Working Days Calculator Logic

Flowchart and pseudocode illustrating DAX logic to calculate working days for "Bezahlter Urlaub" (paid vacation) based on specified date range and conditions.


Empty image or helper icon

Prompt

Bezahlter Urlaub geplant = VAR slicerStart = MIN('Date'[Date])
VAR slicerEnd = MAX('Date'[Date])
VAR todayIncluded = TODAY() >= slicerStart && TODAY() <= slicerEnd
VAR dateFiltered = ISFILTERED('Date')
VAR futureSlicer = slicerStart > TODAY()
VAR pastSlicer = slicerEnd < TODAY()

RETURN IF(
    pastSlicer, 
    0, 
    SUMX(
        FILTER(
            TimeOffs_fact,
            TimeOffs_fact[time_off_type] = "Bezahlter Urlaub"
        ),
        VAR actualStart = SWITCH(
            TRUE(),
            todayIncluded, MAX( TODAY(), TimeOffs_fact[start_date] ),
            futureSlicer, MAX( slicerStart, TimeOffs_fact[start_date] ),
            NOT( dateFiltered ), MAX( TODAY() + 1, TimeOffs_fact[start_date] )
            )
           
        VAR actualEnd = SWITCH(
            TRUE(),
            todayIncluded, MIN( slicerEnd, TimeOffs_fact[end_date] ),
            futureSlicer, MIN( slicerEnd, TimeOffs_fact[end_date] ),
            NOT( dateFiltered ), TimeOffs_fact[end_date]
            )
           
        VAR workingDaysInSlicerPeriod = CALCULATE(
            COUNTROWS(
                FILTER(
                    ALL('Date'),
                    'Date'[Date] >= actualStart &&
                    'Date'[Date] <= actualEnd &&
                    NOT('Date'[Weekday] IN {1, 7}) &&
                    'Date'[Feiertag] = FALSE
                )
            ),
            ALL('Date')
        )
        RETURN workingDaysInSlicerPeriod
    )
)

Answer

Visualization of Logic for "Bezahlter Urlaub geplant"

Overview

The provided DAX code calculates the number of working days within a given date range for "Bezahlter Urlaub" (paid vacation) based on various conditions. Below is a visual representation in the form of a flowchart and detailed pseudocode, to simplify understanding the code's logic and structure.

Flowchart

flowchart TD
  A[Start] --> B[Define slicerStart: MIN('Date'[Date])]
  B --> C[Define slicerEnd: MAX('Date'[Date])]
  C --> D[Compute todayIncluded: TODAY() >= slicerStart && TODAY() <= slicerEnd]
  D --> E[Check dateFiltered: ISFILTERED('Date')]
  E --> F[Check futureSlicer: slicerStart > TODAY()]
  F --> G[Check pastSlicer: slicerEnd < TODAY()]
  G --> H[IF pastSlicer THEN RETURN 0]
  H --> I[ELSE Process "Bezahlter Urlaub"]
  I --> J[Filter TimeOffs_fact where time_off_type = "Bezahlter Urlaub"]
  J --> K[Calculate actualStart using SWITCH]
  K --> L[Calculate actualEnd using SWITCH]
  L --> M[Calculate workingDaysInSlicerPeriod]
  M --> N[RETURN workingDaysInSlicerPeriod]

style A fill:#f9f,stroke:#333,stroke-width:2px;
style N fill:#f9f,stroke:#333,stroke-width:2px;

Detailed Pseudocode

1. Define slicerStart as the minimum date from 'Date' table.
2. Define slicerEnd as the maximum date from 'Date' table.
3. Compute todayIncluded to check if today's date falls within the range of slicerStart and slicerEnd.
4. Check if the 'Date' table is filtered (dateFiltered).
5. Check if slicerStart is a future date relative to today (futureSlicer).
6. Check if slicerEnd is a past date relative to today (pastSlicer).
7. If pastSlicer is true, return 0.
8. Otherwise, process "Bezahlter Urlaub" as follows:
     a. Filter the rows in TimeOffs_fact where the time_off_type is "Bezahlter Urlaub".
     b. Define actualStart using SWITCH:
         - If todayIncluded, use the later of TODAY() or the TimeOffs_fact start_date.
         - If futureSlicer, use the later of slicerStart or the TimeOffs_fact start_date.
         - If dateFiltered is FALSE, use the later of TODAY() + 1 or the TimeOffs_fact start_date.
     c. Define actualEnd using SWITCH:
         - If todayIncluded, use the earlier of slicerEnd or the TimeOffs_fact end_date.
         - If futureSlicer, use the earlier of slicerEnd or the TimeOffs_fact end_date.
         - If dateFiltered is FALSE, use the TimeOffs_fact end_date.
     d. Calculate workingDaysInSlicerPeriod as the number of working days between actualStart and actualEnd excluding weekends and holidays.
9. Return workingDaysInSlicerPeriod.

Annotations

  • slicerStart: The start date of the date range selected by the user.
  • slicerEnd: The end date of the date range selected by the user.
  • todayIncluded: Boolean flag indicating if today's date falls within the selected date range.
  • dateFiltered: Boolean flag indicating if the 'Date' table is filtered.
  • futureSlicer: Boolean flag indicating if the selected date range starts in the future.
  • pastSlicer: Boolean flag indicating if the selected date range ended in the past.
  • actualStart: The effective start date for calculating working days.
  • actualEnd: The effective end date for calculating working days.
  • workingDaysInSlicerPeriod: The number of working days (excluding weekends and holidays) within the effective start and end dates.

For a more thorough learning experience on DAX and Power BI, consider exploring courses available at the Enterprise DNA Platform.

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

Flowchart and pseudocode illustrating DAX logic to calculate working days for "Bezahlter Urlaub" (paid vacation) based on specified date range and conditions.

More Logic Visualizers
Apache Flink Logic Visualizer Apache Flink Logic VisualizerApache Pig Logic Visualizer Apache Pig Logic VisualizerAzure Data Factory Logic Visualizer Azure Data Factory Logic VisualizerC/C++ Logic Visualizer C/C++ Logic VisualizerCouchDB Logic Visualizer CouchDB Logic VisualizerDAX Logic Visualizer DAX Logic VisualizerExcel Logic Visualizer Excel Logic VisualizerFirebase Logic Visualizer Firebase Logic VisualizerGoogle BigQuery Logic Visualizer Google BigQuery Logic VisualizerGoogle Sheets Logic Visualizer Google Sheets Logic VisualizerGraphQL Logic Visualizer GraphQL Logic VisualizerHive Logic Visualizer Hive Logic VisualizerJava Logic Visualizer Java Logic VisualizerJavaScript Logic Visualizer JavaScript Logic VisualizerJulia Logic Visualizer Julia Logic VisualizerLua Logic Visualizer Lua Logic VisualizerM (Power Query) Logic Visualizer M (Power Query) Logic VisualizerMATLAB Logic Visualizer MATLAB Logic VisualizerMongoDB Logic Visualizer MongoDB Logic VisualizerOracle Logic Visualizer Oracle Logic VisualizerPostgreSQL Logic Visualizer PostgreSQL Logic VisualizerPower BI Logic Visualizer Power BI Logic VisualizerPython Logic Visualizer Python Logic VisualizerR Logic Visualizer R Logic VisualizerRedis Logic Visualizer Redis Logic VisualizerRegex Logic Visualizer Regex Logic VisualizerRuby Logic Visualizer Ruby Logic VisualizerSAS Logic Visualizer SAS Logic VisualizerScala Logic Visualizer Scala Logic VisualizerShell Logic Visualizer Shell Logic VisualizerSPSS Logic Visualizer SPSS Logic VisualizerSQL Logic Visualizer SQL Logic VisualizerSQLite Logic Visualizer SQLite Logic VisualizerStata Logic Visualizer Stata Logic VisualizerTableau Logic Visualizer Tableau Logic VisualizerVBA Logic Visualizer VBA Logic Visualizer