Count of Days Not Worked

[SimonNU]

I'm attempting to count the days not worked. Any suggestions?

Fact Table:
Date_Key | Employee Name | Date Hired | Date Terminated | Hours

Date Table:
Date_Key | Date | WeekendOrWeekday | IsHoliday

So far I have the below working but unfortunately it's counting days before the employee was hired and after they were terminated. How can I filter it to just the context between the Employee's Date Hired and Date Terminated?

Count of Non-Holiday Weekdays Not Worked :=CALCULATE (
    COUNTX (
        VALUES ( 'Date Table'[Date] ),
        IF ( [Sum of Hours] = 0, 1 )
    ),
    'Date Table'[IsHoliday] = FALSE (),
    'Date Table'[WeekendOrWeekday] = "Weekday"
)

Thanks!

 

[scottsen]

See if this gives any good ideas. Two methods to calculate Person-Hours | Power Pivot | Tiny Lizard

• Your fact table is a bit weird to me... what does the first column represent?
• Your COUNTX + VALUES is clever! Since [Date] is the primary key, I suspect the VALUES isnt' needed -- but love the creativity here.
• The easy way... is probably [Total Work Days] - [Days Actually Worked] sorta thing...
• Total Work Days := CALCULATE(COUNTROWS('Date Table'), 'Date Table'[IsHoliday] = FALSE, Date Table'[WeekendOrWeekday] = "Weekday")
• Days Worked := DISTINCTCOUNT(Facts[Date_Key])

Might need a SUMX of the latter of employees?

 

[SimonNU]

Thanks Scott!

I ended up going the third route you suggested, [Total Work Days] - [Days Actually Worked]. It felt like a bit of a hack but it works for my purposes.