cancel
Showing results for
Did you mean:

# Net Work Days

Super User IV
18142 Views

## Re: Net Work Days

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

Super User IV

## Re: Net Work Days

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

```NetWorkDays =
VAR Calendar1 = CALENDAR(TODAY(),[Any Given Date])
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!

Proud to be a Datanaut!

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

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

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

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

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Net Work Days

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.

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

## 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),
)
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!