Capacity Calculation

[sjrrkb123]

Suppose I have two tables,
Table 1: Dates with Calendar Date, isWeekday, isHoliday
Table 2: Professionals with Professional Name, start date, end date

I am trying to calculate capacity using date values in a slicer (Calendar Date) to make these dynamic with a measure. Here is the measure I have made:

IT Total Days Worked v2 = 
var start_end = 
    CALCULATE(COUNTROWS('Dates - Dim Dates'),
        DATESBETWEEN('Dates - Dim Dates'[CalendarDate],[IT Employment Start Date],[IT Employment End Date]),
        KEEPFILTERS('Dates - Dim Dates'[IsWeekDay] = TRUE()),
        KEEPFILTERS('Dates - Dim Dates'[IsHoliday] = FALSE()))
var start_only = 
    CALCULATE(COUNTROWS('Dates - Dim Dates'),
        DATESBETWEEN('Dates - Dim Dates'[CalendarDate],[IT Employment Start Date],MAX('Dates - Dim Dates'[CalendarDate])),
        KEEPFILTERS('Dates - Dim Dates'[IsWeekDay] = TRUE()),
        KEEPFILTERS('Dates - Dim Dates'[IsHoliday] = FALSE()))
var between = 
    CALCULATE(COUNTROWS('Dates - Dim Dates'),
        KEEPFILTERS('Dates - Dim Dates'[IsWeekDay] = TRUE()),
        KEEPFILTERS('Dates - Dim Dates'[IsHoliday] = FALSE()))
var min_end = 
    CALCULATE(COUNTROWS('Dates - Dim Dates'),
        DATESBETWEEN('Dates - Dim Dates'[CalendarDate],MIN('Dates - Dim Dates'[CalendarDate]),[IT Employment End Date]),
        KEEPFILTERS('Dates - Dim Dates'[IsWeekDay] = TRUE()),
        KEEPFILTERS('Dates - Dim Dates'[IsHoliday] = FALSE()))
var start_date = [IT Employment Start Date]
var end_date = [IT Employment End Date]
var active = [IT Active Flag]
var last_cal_date = [Last Calendar Date]
var first_cal_date = [First Calendar Date]
var holiday_ratio = 343/365

RETURN
IF(NOT(active), 
    IF(start_date <= first_cal_date && end_date >=last_cal_date, between,
    IF(start_date <= first_cal_date && end_date < last_cal_date, min_end,
    IF(start_date > first_cal_date && end_date < last_cal_date, start_end,
    IF(start_date > first_cal_date && end_date >= last_cal_date, start_only)))),
    IF(start_date <= first_cal_date, between, 
    IF(start_date > first_cal_date, start_only))) * holiday_ratio

My problem is this is very slow. I suspect it is using row based calculation rather than set based calculations in SQL server. However, I am at a loss of how to make this faster and was hoping to find suggestions here.

[sjrrkb123]

@TomMartens Thank you very much for the info and the links. I ended up not using your approach and changed my calculation to drastically simplify my DAX. It accounts for both the start/end dates of employees and the dynamic slider values for my use case. Here is what I did:

Total Businee Days Worked = 
var between = 
    SUMX(Professionals,
        CALCULATE(COUNTROWS('Dates - Dim Dates'),
            'Dates - Dim Dates'[IsWeekDay] = TRUE(),
            'Dates - Dim Dates'[IsHoliday] = FALSE(),
            DATESBETWEEN('Dates - Dim Dates'[CalendarDate], [Most Recent Hire Date], [Employment End Date]),
            DATESBETWEEN('Dates - Dim Dates'[CalendarDate], [First Calendar Date], [Last Calendar Date]),
            USERELATIONSHIP(Ticket[SUBMITTER_ID], Professionals[ID])))
var start_date = [Most Recent Hire Date]
RETURN
IF(ISBLANK(start_date), 0, between)

        

[TomMartens · Accepted solution]

Hey @sjrrkb123 ,

the task you are facing has a name, it's called events-in-progress. This article provides anything you need:

https://blog.gbrueckl.at/events-in-progress/

This article contains all the relevant links.

Make sure that you read this article first: 

http://sqljason.com/2012/11/classifying-and-solving-events-in.html

For quite some time I pursue a different approach, by creating an additional table based on the information of the employee table DateStart and DateEnd. This table contains a single row for each employee, each day.
From my experience, it does not matter if this table contains millions of rows, even 10s of millions is no problem 🙂 My largest "expanded" table currently contains ~200millions rows. All measures will become more simple and for this - much more faster.
Here is a screenshot of my final data model:

This is my employee table:

I use this DAX statement to create the new table:

EmployeeIsActive = 
var CalendarEnddate = MAX('Calendar'[Date])
return
ADDCOLUMNS(
    GENERATE(
        'Employee'
        , var EmployeeEndDate = 'Employee'[DateEnd]
        var SeriesEndDate = IF(ISBLANK(EmployeeEndDate) , CalendarEnddate , EmployeeEndDate)
        return
        GENERATESERIES('Employee'[DateStart] , SeriesEndDate , 1)
    )
    , "simpleValue" , 1
)

If there is no EndDate in the EmployeeTable I replace the empty value with the max calendar date.
This example is a little simplified, for this, I do not rename the column Value, that is automatically created by the GENERATESERIES function.
I create 1-to-many relationships with the new table on the many side of the relationships.

Now I can create more simple measures e.g. a DISTINCTCOUNT of the employee id from the new table. I "just" have to consider how I have to treat EndDates that are not on the end date of a month, but this is comparatively simple.

A simple screenshot with a DISTINCTCOUNT of the EmployeeID from the new table, for this simple example no complex measure is needed.

Hopefully, this will help to tackle your challenge.

Regards,
Tom

[sjrrkb123]

@TomMartens this is great...I will look into applying this tomorrow and get back to you.