topic Re: DAX Measure vs Calculated Column to perform a capacity calculation based on various dates in DAX Commands and Tips

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

Steyan111 — Sat, 01 Mar 2025 14:59:51 GMT

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

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

lbendlin — Sat, 01 Mar 2025 18:03:40 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

Steyan111 — Sun, 02 Mar 2025 13:02:49 GMT

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=8g0p0urnbruqprqt95dl29njr&st=8onrotch&dl=0

Background:

Hope this helps.

Thanks Regs Andre

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

lbendlin — Sun, 02 Mar 2025 19:12:53 GMT

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.

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

Steyan111 — Mon, 03 Mar 2025 16:37:20 GMT

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

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

lbendlin — Mon, 03 Mar 2025 20:39:42 GMT

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.

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

Steyan111 — Tue, 04 Mar 2025 12:00:22 GMT

Thanks so much; you're a rockstar!