DAX Measure vs Calculated Column to perform a capacity calculation based on various dates

[Steyan111]

Hi there

The following calculated column Dax Formula works; however, the equivalent as a Measure only produces 4's and not the correct values (0 and 2) based on selected dates:

Background:

I need to calculate the capacity (number of jobs) employees can do per day based on the below date criteria:
Startdate = Employee start date
TermDate = Employ Termination date 
2JobCapDate = The date from when the employee can manage 2 jobs per day
4JobCapDate = The date from when the employee can manage 4 jobs per day

Logic:
If the startdate column date is before the selected date and
the Termdate column date is blank and
the selected date is not a weekday or a bankholiday,
        then let the value be 0, if the selected date is before the 2JobCapDate column date, or
        let the value be 2 if the selected date is after the 2JobCapDate column date but before the 4JobCapDate or
        let the value be 4 if the selected is on or after the 4JobCapDate column date.

Calculated Column (each column is named a specific date, example 1-Feb-2025):

1-Feb-2025 =

VAR ReferenceDate = DATE(2025, 2, 1)

VAR StartDate = calFibreCapacityTable[StartDate]

VAR TermDate = calFibreCapacityTable[TermDate]

VAR JobCapDate2 = 'calFibreCapacityTable'[2JobCapDate]

VAR JobCapDate4 = 'calFibreCapacityTable'[4JobCapDate]

// Check if 1st Feb 2025 is a weekday or a bank holiday

VAR IsWorkdayNotHoliday =

    LOOKUPVALUE('Date'[IsWorkingDay], 'Date'[Date], ReferenceDate) = TRUE()

RETURN

    IF(

        StartDate < ReferenceDate && ISBLANK(TermDate) && IsWorkdayNotHoliday,

        SWITCH(

            TRUE(),

            ReferenceDate < JobCapDate2, 0,

            ReferenceDate >= JobCapDate2 && ReferenceDate < JobCapDate4, 2,

            ReferenceDate >= JobCapDate4, 4,

            BLANK()  // Default case

        ),

        BLANK()  // If conditions aren't met, return BLANK

    )

Measure:

Capacity Measure =

VAR ReferenceDate = SELECTEDVALUE('Date'[Date]) // Dynamically selected date

VAR StartDate = SELECTEDVALUE(calFibreCapacityTable[StartDate])

// Activate relationships for [TermDate], [2JobCapDate], and [4JobCapDate] when required

VAR TermDate =

    CALCULATE(

        SELECTEDVALUE(calFibreCapacityTable[TermDate]),

        USERELATIONSHIP(calFibreCapacityTable[TermDate], 'Date'[Date])

    )

VAR JobCapDate2 =

    CALCULATE(

        SELECTEDVALUE(calFibreCapacityTable[2JobCapDate]),

        USERELATIONSHIP(calFibreCapacityTable[2JobCapDate], 'Date'[Date])

    )

VAR JobCapDate4 =

    CALCULATE(

        SELECTEDVALUE(calFibreCapacityTable[4JobCapDate]),

        USERELATIONSHIP(calFibreCapacityTable[4JobCapDate], 'Date'[Date])

    )

// Check if the selected date is a working day (not a holiday)

VAR IsWorkdayNotHoliday =

    LOOKUPVALUE('Date'[IsWorkingDay], 'Date'[Date], ReferenceDate) = TRUE()

RETURN

    IF(

        StartDate < ReferenceDate && ISBLANK(TermDate) && IsWorkdayNotHoliday,

        SWITCH(

            TRUE(),

            ReferenceDate < JobCapDate2, 0,

            ReferenceDate >= JobCapDate2 && ReferenceDate < JobCapDate4, 2,

            ReferenceDate >= JobCapDate4, 4,

            BLANK()  // Default case

        ),

        BLANK()  // If conditions aren't met, return BLANK

    )  

Many thanks

[Steyan111]

Hi There

Here is a test model with data table and measure created:
https://www.dropbox.com/scl/fi/kj10u9t9dqzrm89ngznl6/Test-Capacity-Planner.pbix?rlkey=8g0p0urnbruqpr... 

Background:

I need to calculate the capacity (number of jobs) employees can do per day based on the below date criteria:
Startdate = Employee start date
TermDate = Employ Termination date 
2JobCapDate = The date from when the employee can manage 2 jobs per day
4JobCapDate = The date from when the employee can manage 4 jobs per day

Logic:
If the startdate column date is before the selected date and
the Termdate column date is blank and
the selected date is not a weekday or a bankholiday,
        then let the value be 0, if the selected date is before the 2JobCapDate column date, or
        let the value be 2 if the selected date is after the 2JobCapDate column date but before the 4JobCapDate or
        let the value be 4 if the selected is on or after the 4JobCapDate column date.

Hope this helps.

Thanks Regs Andre

[lbendlin]

The bankholiday  column has no usable data, so I'll go with the IsBusinessDay  column.

You need to disconnect the date table (i did it by deactivating the relationship).

Then create a cross join between calendar dates and employees.

and then implement the date logic.

[Steyan111]

Hi Ibendlin.

Looks great thanks!

I've followed your instructions.

The only thing that I'm unsure about is creating the cross join.

Would I do this in the query?  

Thanks

Regs

Andre

[lbendlin · Accepted solution]

in my example it is done in the visual itself.  But if you want you can create one in DAX too, via CROSSJOIN().  Note that I cheaped out and do not show row totals. If you need those then you need to implement the full cross join experience.

[Steyan111]

Thanks so much; you're a rockstar!

[lbendlin]

You provided a wall of code without context. That makes it difficult to help.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...