cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shefalinishad11
Helper I
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

 

Milestone.jpg

 

Due DateActual DateWorking Days
03-06-2110-06-21-6.0
04-06-2111-06-21-6.0
05-06-2112-06-21-5.0
06-06-21  
07-06-21  
08-06-2109-05-2122.0
09-06-2110-05-2123.0
10-06-2111-05-2123.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.


Calendar.jpg

 

 

 

 

 

 

This is all the actual data fields I shared.

1 ACCEPTED SOLUTION
CNENFRNL
Super User
Super User

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

Screenshot 2021-09-08 222103.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

8 REPLIES 8
CNENFRNL
Super User
Super User

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

Screenshot 2021-09-08 222103.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

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

sevenhills
Super User
Super User

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/

Hey @sevenhills ,

 

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

 

Screenshot2.jpg

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

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 )

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

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.sqlbi.com/articles/counting-working-days-in-dax/ 

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

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!