Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ArchStanton
Responsive Resident
Responsive Resident

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  

 

 

 

5 REPLIES 5
Greg_Deckler
Super User
Super User

@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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

 

ArchStanton_0-1661180315223.png

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

 

 

Hi @ArchStanton ,

 

You could create a measure like

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

vstephenmsft_0-1661418555691.png

 

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.

 

 

Hi Stephan,

 

I tried your code but got this message:

 

ArchStanton_0-1661419812442.png

 

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

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Users online (76)