Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
4 years ago

Calculate the Working Days between two dates

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 ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

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's avatar
ArchStanton
Power Participant
4 years ago

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!