Create a Measure for the Number of days between two dates, but only counting work/business days

[GuillermoPuls]

Hi,

I would like to get a measure that is returning the number of days that a contract is current in a given month.

I have a table with dates (It's a unique list with continuous dates, Week day, moth, year, etc...)

             table with sales (Contains: Accounts, Dates, Product, Revenue, etc...)

             table with Accounts (Unique list of Accounts

             table with contracts (Unique list of Contracts, Account, Start Date, End Date)

The relationships are modelled as follows

When I try the following I get the week days for the whole range dim_Date[Date] 

Working days= 
Calculate(COUNT(dim_Date[Date]),
	     FILTER('dim_Date',AND('dim_Date'[DayOfWeekNumber]<>6,'dim_Date'[DayOfWeekNumber]<>7)),
	    FILTER(ALL(dim_Date),dim_Date[Date]<=calculate(MAX(dim_Date[Date])))
)

When I try the following I get the number of days they order as per the Sales table which at least identify the start and finish date. My problem is that it is counting dates in the sales table instead of counting in the date table.

Count Contracts Days= 
CALCULATE (
    COUNTA( dim_Date[Date] ),
    FILTER ( FILTER(dim_Date,dim_Date[Date] >= CALCULATE ( MAX ( 'Contracts'[Start Date]))),
                    AND(dim_Date[DayOfWeekNumber] <> 6,dim_Date[DayOfWeekNumber] <> 7 )),
    FILTER (dim_Date,dim_Date[Date] <= CALCULATE (MAX('Contract'[End Date])))
)

[spuder · Accepted solution]

Hi,

as far as I can see the problem is that you try to use 2 different date columns on the datetable without having 2 different relationships.

My idea would be to try it with mathematic

1. Count all rows

2. Count all rows <= Start date

3. Count all rows >= End date

4.  1. - 2. - 3. = result

Try to use variables. Should looks like this.

CCD = 

var allrows = COUNTROWS(dim_Date[Date])

var exstart = CALCULATE(COUNTROWS(dim_Date[Date]),FILTER(dim_Date, dim_Date(Date)<=MAX(Start_date))

var exend =
CALCULATE(COUNTROWS(dim_Date[Date]),FILTER(dim_Date, dim_Date(Date)>=MAX(End_date);USERELATIONSHIP(dim_Date(date),end_date))

return

allrows - exstart - exend

Important is that you define in calculate a new relationship between the second date column and the datekey as mentioned in the beginning of my post.

I'm not sure if it works but this would be my way. Good luck

[GuillermoPuls]

Thanks Spuder, it's an interesting approach that works! I haven't started to deploy variables and it looks very practical. 

I spent some more time looking at the relationships and by making them single I got the desired result.

Thanks