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!

  • 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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

         

        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