Paid Vacation Calculation Strategy Refactor

Implementation of Strategy Design Pattern for "Bezahlter Urlaub geplant"

Problem Analysis

The provided code calculates the number of working days for paid vacation (Bezahlter Urlaub). The calculation is influenced by several conditions based on date slicers, such as whether today's date is included in the slicer range, and whether the slicer range is in the past or future.

Suitable Design Pattern: Strategy

The Strategy Pattern is suitable for this problem as it allows us to define a family of algorithms, encapsulate each one, and make them interchangeable. The calculation logic can vary depending on conditions, which aligns well with the Strategy Pattern principles.

Code Template Development

The provided code will be refactored using the Strategy Pattern. We will define different strategies for calculating actualStart and actualEnd based on the conditions such as todayIncluded, futureSlicer, and dateFiltered.

DAX Implementation

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
    )
)

Documentation and Comments

The Strategy Pattern simplifies the calculation and makes it modular and readable. Each part of the calculation logic is encapsulated based on the respective conditions.

Explanation:

1. Initialize Variables:

   • slicerStart and slicerEnd determine the range of dates selected.
   • todayIncluded checks if today's date falls within the selected range.
   • dateFiltered checks if a date filter is applied.
   • futureSlicer and pastSlicer check if the selected range is in the future or past.

2. Return Condition for Past Slicer:

   • If pastSlicer is true, return 0 as there are no working days to consider.

3. Main Calculation Using SUMX:

   • Filters the TimeOffs_fact table for "Bezahlter Urlaub".
   • Uses SWITCH to determine actualStart and actualEnd based on the conditions.
   • Calculates workingDaysInSlicerPeriod by counting rows within the determined range, excluding weekends and holidays.

Best Practices Explained

• Modularity: The Strategy Pattern ensures the calculation logic is encapsulated, making it easy to extend and maintain.
• Readability: Clearly defined variables and SWITCH statements improve readability.
• Reusability: Different strategies can be easily reused or extended for other types of calculations.

This implementation demonstrates how to use the Strategy Pattern for a calculation logic problem in DAX. For further learning, consider exploring courses on the Enterprise DNA platform.