Calculate the Working Days between two dates

[ArchStanton]

Hi,

I have a Date Table that tells me if the day is a Working Day or not:

Is Working Day = IF('Date'[Weekday]>5,0,IF('Date'[Public Holiday]>1,0,1))

The answers is simply: True / False

(Data Type is True/False)

In another table I am trying to calculate the Working Days between 'Deferrals'[actualstart], 'Deferrals'[actualend] based on the Unique Identifier: 'Deferrals'[TicketNo]

I am trying to calculate this in a new column within the Deferrals table but I cannot access the Date table when I try.

Can someone help?  I though anout using NETWORKDAYS but there is no need as I already have my IS Working DAY column

Can someone show me how to do this as a calculated column and a Measure please?

Thanks!

Should this  

[Greg_Deckler]

@ArchStanton So, as a column you should be able to do this:

Net Work Days Column =
  VAR __Start = 'Deferrals'[actualstart]
  VAR __End = 'Deferrals'[actualend]
  VAR __Table = FILTER(ALL('Dates'),[Date] >= __Start && [Date] <= __End && [Is Working Day] = TRUE())
RETURN
  COUNTROWS(__Table)

Net Work Days Measure =
  VAR __Start = MAX('Deferrals'[actualstart])
  VAR __End = MAX('Deferrals'[actualend])
  VAR __Table = FILTER(ALL('Dates'),[Date] >= __Start && [Date] <= __End && [Is Working Day] = TRUE())
RETURN
  COUNTROWS(__Table)

[ArchStanton]

Hi Greg,

Something isn't right with the measure even though the calculated column works when its filtered on the same ticket number.

I've tweaked the first variable so its shows MIN because MAX is only showing 89 days (the correct answer is 143 days)

With the start VARIABLE as MIN i get 159 days!

However, the calculated column works:

Both are filtered on the same Ticket number. The actual start & actualend dates are:

actualstart	actualend
15-Jan-20	19-Feb-20
19-Feb-20	19-Feb-20
21-Feb-20	30-Mar-20
23-Apr-20	26-Aug-20

[v-stephen-msft]

Hi @ArchStanton ,

You could create a measure like

Measure = COUNTROWS(FILTER('Date',[Date]>=MAX('Deferrals'[actualstart])&&[Date]<=MAX('Deferrals'[actualend])&&[Is Working Day]=1))

Measure and calculated column are different. You could refer to 

Power BI: Calculated Measures vs. Calculated Columns | by Rod Castor | Towards Data Science

Calculated Column and Measure in Power BI (powerbiconsulting.com)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArchStanton]

Hi Stephan,

I tried your code but got this message:

I do have something that works already but it's computing an extra day for some tickets which could be something to do with TimeStamped values? It doesn't affect every ticket no so this can be tolerated. I would have liked to test your version as well if possible?

Thanks

[ArchStanton]

Thank you Greg, I didn't realise that the Column & Measure could be identical - before I accept as a solution, is that always the case?

ps, I'm still learning so apologies for what may seem like a daft question!