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

(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  

 

 

 

Need Help

5 Replies

  • 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!

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      4 years ago

      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