cancel
Showing results for 
Search instead for 
Did you mean: 

Net Work Days

Super User IV
18142 Views
Tan Advocate I
Advocate I

Re: Net Work Days

Is there a way to calculate difference between Today() and a given date?

Super User IV
Super User IV

Re: Net Work Days

@Tan - Sure, you could do something like this:

 

NetWorkDays = 
VAR Calendar1 = CALENDAR(TODAY(),[Any Given Date])
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

If necessary, reverse the parameters for the CALENDAR function.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Tan Advocate I
Advocate I

Re: Net Work Days

Thanks @Greg_Deckler  this works like a charm.

Anonymous
Not applicable

Re: Net Work Days

I am receiving the same error. Did you ever figure out the solution?

 

Thanks!

Tan Advocate I
Advocate I

Re: Net Work Days

I infact did i used Greg's solution and created another measure to calculate weekends and holidays and subtracted from the above solution.  


@Anonymous wrote:

I am receiving the same error. Did you ever figure out the solution?

 

Thanks!


 

kman42 Helper II
Helper II

Re: Net Work Days

Sorry to re-open an old thread, but I'm not getting the right answer for the number of days using the COUNTX function in the NetWorkDaysHoursMinutes measure. If I have a start date of 10/11/2019 and an end date of 10/12/2019, depending on the start and end times, it could be 0 hours or 1 hour. E.g.

 

10/11/2019 11:00 to 10/12/2019 9:00 should return "0 Days 22 hours 0 minutes", but it returns "1 Day 22 hours 0 minutes"

 

Is there a way to fix this?

 

 

Super User IV
Super User IV

Re: Net Work Days

@kman42, Ahhhh, the boundary cases. Glad you did re-open the thread. Try this variation out, should account for the boundary case that you specify and frankly cleans up the code a bit.

 

NetWorkDaysHoursMinutes = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
VAR Days = COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
VAR Hours = HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1))
VAR Minutes = MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1))
VAR NetWorkDaysHoursMinutes = 
    SWITCH(TRUE(),
        Days = 1 && HOUR(MAX(NetWorkDays[review date])) < HOUR(MAX(NetWorkDays[created date])),
            "0 Days " & Hours & " Hours " & Minutes & " Minutes",
        Days & " Days " & Hours & " Hours " & Minutes & " Minutes"
    )
RETURN NetWorkDaysHoursMinutes 

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Jadnerb
Frequent Visitor

Re: Net Work Days

Based on your "NetWorkDaysHoursMinutes"

 

I tried using MAX(TODAY()) but it didn't work, I used MIN and MAX.  However, I am getting text (Days Hours Minutes) and cannot change the format to whole number.

 
texmexdragon Post Partisan
Post Partisan

Re: Net Work Days

@Greg_Deckler   I tried using your formula early on, but it would not allow me since I have some Ship Dates that start before Due Date.    The error message said the formula could not allow for that. 

 

 

Super User IV
Super User IV

Re: Net Work Days

Oh, that would be the CALENDAR function. You would just have to create an IF statement to check which date is larger or smaller and then create your CALENDAR appropriately. Like this:

 

NetWorkDays = 
VAR __CreatedDate = MAX(NetWorkDays[created date])
VAR __ReviewDate= MAX(NetworkDays[review date])
VAR Calendar1 = 
  IF(
    __CreatedDate < __ReviewDate,
    CALENDAR(__CreatedDate,__ReviewDate),
    CALENDAR(__ReviewDate,__CreatedDate),
  )
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

 

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!