DAX Measure does not ignore the weekend filter, therefore results are empty

[Baerbel]

Dear experts,
I'm struggling with a DAX-Measure and hope somebody could help me.🤔
I want to calculate the share of employees on holiday compared to the total number of employes; my visual is flexible with field parameters for the x-axis, where I would like to display results once per month and once per weekday (but here except suterday and sunday). I have two tables, one with the holidays displayed per day (each single holiday) and a parameter Table, where I have listed the emplyoees as per month. For ability to create a relationship to my calendar tabele I've a date-column here fixed to the last day of the respective month.
No I have the challenge, that my measure don't show the results for the number of employees, when I'm filtering my visual excluding sutterdays and sundays - I tried to manage it in different ways, but either I can fix this problem but not filter by Department anymore, or the number of employees is not shown for selected month 🤔
Can anybody please help me? 🙄The code is attached below
Thanks so much in advance 😍
Bärbel

Share_onholiday = 
VAR MaxYear = YEAR(MAX(Calender[Date]))  
VAR MaxMonth = MONTH(MAX(Calender[Date]))  
VAR MaxDate =
    CALCULATE(
        MAX(Calender[Date]),  
        FILTER(
            Calender,
            YEAR(Calender[Date]) = MaxYear
            MONTH(Calender[Date]) = MaxMonth  
        )
    )
-- number of employees per month in a selected time period even if the [Date] is on a weekend
VAR Employees =
    CALCULATE(
       SUM(FACT_Parameter[Value]),
       REMOVEFILTERS ( FACT_Parameter[Date] ),
       MONTH ( FACT_Parameter[Date] ) MONTH( MaxDate ),
       YEAR ( FACT_Parameter[Date] ) = YEAR ( MaxDate ),
       FACT_Parameter[Parameter] = "Employees",
       FACT__Parameter[Value] > 0 
      )
-- number of employess on holiday
VAR onHolidays =
    CALCULATE(
        DISTINCTCOUNT(FACT_Holidays[Emp_ID]),
        FILTER(
            Calender,
            WEEKDAY(Calender[Date], 2) < 6 &&  
            YEAR(Calender[Date]) = MaxYear &&  
            MONTH(Calender[Date]) = MaxMonth  
        )
    )
VAR Result = DIVIDE(onHolidays, Employees)  
RETURN Result

[v-jiewu-msft]

Hi @Baerbel ,

Based on the description, try using the following DAX formula to calculate employees and onHolidays.

VAR Employees = 
    CALCULATE(
       SUM(FACT_Parameter[Value]),
       REMOVEFILTERS ( FACT_Parameter[Date] ),
       MONTH ( FACT_Parameter[Date] ) = MONTH( MaxDate ),
       YEAR ( FACT_Parameter[Date] ) = YEAR ( MaxDate ),
       FACT_Parameter[Parameter] = "Employees",
       FACT__Parameter[Value] > 0,
       NOT ( WEEKDAY(FACT_Parameter[Date], 2) IN {6, 7} )
    )

VAR onHolidays = 
    CALCULATE(
        DISTINCTCOUNT(FACT_Holidays[Emp_ID]),
        FILTER(
            Calender,
            YEAR(Calender[Date]) = MaxYear &&
            MONTH(Calender[Date]) = MaxMonth &&
            WEEKDAY(Calender[Date], 2) < 6 -- Weekdays only
        )
    )

If DAX formula doesn’t work, please provide sample data and visual picture.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Baerbel]

Dear @v-jiewu-msft 

Thanks a lot for the adjusted code and also thanks to @Kedar_Pande 
both codes are working when I'm evaluating the rwsults as per month as ou can see in table 1. When I'm changing the dimension from month to weekdays (to be able to evaluate the number of employees on holidays per weekday for a period of time -lets say January to December - the results are displayed either for Thuesday (refers to december) - table 3- or when using the months  ist shows me only one weekday per month - table 2
If you have any additional ideas - they are highly welcome - in any case Mery Christmas

Bärbel

[Dangar332]

Hi, @Baerbel 

In you var "Holiday" you mention to calculate Employee count on Weekends but from your Condition(Weekday(Calender[Date],2)<6) Seems it count Employee on Weekdays.

Can you furthur clarify your requirment and it would be better if you provide some sample data 

[Baerbel]

Dear Danger332,

I'll count weekdays (means excluding Saturday and Sunday)

the problem with my measure arises when applying e.g. for march 2024 - as I excluded in my visual saturdays and sundays it seems to be a problem for the part where I'm calculating the number of employees, as the [date] in the Fact_Parametwr table always refers to EndOfMonth and 31/3/2024 was a Sunday - if I keep saturdays and sundays in my visual it works for the employees part 🤔

Bärbel

[DataNinja777]

Hi @Baerbel ,

The issue with the measure revolves around filtering holidays correctly while maintaining flexibility to exclude weekends and dynamically aggregate by the selected field parameters for the x-axis (e.g., by month or by weekday). The redundancy in the code stems from the repeated filtering by YEAR(Calendar[Date]) and MONTH(Calendar[Date]), which is unnecessary since MAX(Calendar[Date]) already provides a specific date with these details.

For holidays, the problem arises when excluding weekends like Saturdays and Sundays using WEEKDAY(Calendar[Date]), which conflicts with the ability to filter dynamically by department or by the selected time period. To address this, the measure should ensure that the count of employees on holiday is accurate without losing the ability to apply these filters.

The optimized approach removes the redundant filters for year and month by relying directly on MAX(Calendar[Date]). For the count of employees on holiday, the measure focuses on filtering the Calendar table to exclude weekends using WEEKDAY(Calendar[Date], 2) < 6, ensuring only weekdays are considered. This keeps the calculation consistent and adaptable to changes in the visual's filter context.

The improved measure simplifies the logic while addressing the specific requirement to handle holidays accurately. By removing redundant conditions and ensuring filters are applied only where needed, the measure now dynamically calculates the share of employees on holiday while respecting department filters and the exclusion of weekends. This makes it more efficient and aligned with the intent of the calculation.

Best regards,

[Kedar_Pande]

@Baerbel 

You can try:

Share_onholiday = 
VAR MaxYear = YEAR(MAX(Calender[Date])) 
VAR MaxMonth = MONTH(MAX(Calender[Date])) 
VAR MaxDate =
CALCULATE(
MAX(Calender[Date]), 
FILTER(
Calender,
YEAR(Calender[Date]) = MaxYear &&
MONTH(Calender[Date]) = MaxMonth 
)
)
-- Number of employees per month
VAR Employees =
CALCULATE(
SUM(FACT_Parameter[Value]),
FACT_Parameter[Parameter] = "Employees",
FACT_Parameter[Value] > 0,
YEAR(FACT_Parameter[Date]) = MaxYear,
MONTH(FACT_Parameter[Date]) = MaxMonth
)
-- Number of employees on holiday excluding weekends
VAR onHolidays =
CALCULATE(
DISTINCTCOUNT(FACT_Holidays[Emp_ID]),
FILTER(
Calender,
WEEKDAY(Calender[Date], 2) < 6 && -- Exclude Saturday (6) and Sunday (7)
YEAR(Calender[Date]) = MaxYear && 
MONTH(Calender[Date]) = MaxMonth 
)
)
VAR Result = DIVIDE(onHolidays, Employees) 
RETURN Result

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn