cancel
Showing results for
Did you mean:
Helper I

## calculate working day in positive and negative between two dates column

Hi Guys,

I have to prepare a milestone trend analysis and I have 3 columns; Due Date, Actual Date, Working Days

 Due Date Actual Date Working Days 03-06-21 10-06-21 -6.0 04-06-21 11-06-21 -6.0 05-06-21 12-06-21 -5.0 06-06-21 07-06-21 08-06-21 09-05-21 22.0 09-06-21 10-05-21 23.0 10-06-21 11-05-21 23.0

I need to calculate working days in all three cases; Negative, Positive, and Blank. I have created a calculated column with the below formula but that only gives working days when days are positive, not the other 2 conditions.

``````Planned working days Due =
SUMX(
FILTER(
'Calendar',
'Calendar'[Dates] >= 'Milestone Data'[Due Date]
&& 'Calendar'[Dates] <= 'Milestone Data'[Field Work Start]
),
'Calendar'[if work day]
)``````

I have calculated the calendar also with the below fields.

This is all the actual data fields I shared.

1 ACCEPTED SOLUTION
Community Champion
``````NetWorkday =
VAR __min = MIN( 'Milestone Data'[Actual Date], 'Milestone Data'[Due Date] )
VAR __max = MAX( 'Milestone Data'[Actual Date], 'Milestone Data'[Due Date] )
RETURN
IF(
NOT ISBLANK( 'Milestone Data'[Actual Date] ),
SIGN( 'Milestone Data'[Due Date] - 'Milestone Data'[Actual Date] )
* COUNTROWS( FILTER( CALENDAR( __min, __max ), WEEKDAY( [Date], 2 ) < 6 ) )
)``````

8 REPLIES 8
Community Champion
``````NetWorkday =
VAR __min = MIN( 'Milestone Data'[Actual Date], 'Milestone Data'[Due Date] )
VAR __max = MAX( 'Milestone Data'[Actual Date], 'Milestone Data'[Due Date] )
RETURN
IF(
NOT ISBLANK( 'Milestone Data'[Actual Date] ),
SIGN( 'Milestone Data'[Due Date] - 'Milestone Data'[Actual Date] )
* COUNTROWS( FILTER( CALENDAR( __min, __max ), WEEKDAY( [Date], 2 ) < 6 ) )
)``````

Helper I

Thanks a lot, it worked perfectly for networking day in Planned vs Actual days and day difference also!!

Solution Sage

Try this, if you are doing as calculated column

``````Planned working days Due =
CALCULATE (
COUNTROWS ('Calendar')
, DATESBETWEEN ( 'Calendar'[Dates], 'Milestone Data'[Field Work Start], 'Milestone Data'[Due Date] - 1 )
, 'Calendar'[if work day] = 1
, All ( 'Milestone Data' )
)``````

Check this link which provides full details...

https://www.sqlbi.com/articles/counting-working-days-in-dax/

Helper I

Hey @sevenhills ,

It didn't solve the other two things, count negative days and ignore when encounter blank.

Solution Sage

Looks like you are trying is count in all (three) conditions i.e., like pretty much ignoring any conditions.

Then it is as simple as difference between dates.

``````Planned working days Due =
CALCULATE (
COUNTROWS ('Calendar')
, DATESBETWEEN ( 'Calendar'[Dates], 'Milestone Data'[Field Work Start], 'Milestone Data'[Due Date] - 1 )
, All ( 'Milestone Data' )
)``````
Solution Sage

If this is what you need, all your need is this

``Days Diff = DATEDIFF( 'Milestone Data'[Due Date] - 1, 'Milestone Data'[Field Work Start], DAY )``
Helper I

This is giving me normal numbers of days between two dates, not total working days (excluding Weekend - Saturday & Sunday)

Solution Sage

Sorry, it is really not clear what exactly you are looking for ...

Below links covers the dates difference ... considering working days, holidays and other ... Please check

https://blog.enterprisedna.co/calculate-workdays-between-two-dates-in-power-bi/

https://www.mssqltips.com/sqlservertip/6917/working-days-calculator-power-bi/

Announcements