Count number of Days for Jobs done

[kristel_tulio]

Hi I would to ask for help. I need to count the number of days for Job's done then removing the Weekends.
If the Status is "Completed" then StartDate - Completed date( if available), If the Status is "Started" then StartDate - count until today date. "Not Started" should be blank.  If the Start date or the Completed date were blank  I will use the Date in Date table

[v-yueyunzh-msft · Accepted solution]

Hi, @kristel_tulio 

According to your description, You want to calculate the interval days (excluding weekends) according to the following conditions:

(1)[Status] = Not Started ，[Count of Working days]=0

(2) [status] = started, [Count of Working days] is the number of days between [StartDate] and today ().

(3) [status] = completed, if [StartDate] is blank (), then [Count of Working days] is the number of days between 'date' [date] and [CompletedDate]. Otherwise, [Count of Working days] is the number of days between [StartDate] and [CompletedDate]. Right?

Here are the steps you can follow：

(1)This is my test data:   ‘Work Items’ and  ‘Date’ table

(2)We can click “New Column” to create a  calculated column : “Count of Working days”

Count of Working days =
VAR _current_start_date = 'Work Items'[StartDate]
VAR _current_completed_date = 'Work Items'[CompletedDate]
VAR _current_satus = 'Work Items'[Status]
RETURN
    SWITCH (
        TRUE (),
        _current_satus = "Not Started", 0,
        _current_satus = "Started",
            IF (
                _current_start_date <> BLANK (),
                COUNTROWS (
                    FILTER (
                        ADDCOLUMNS (
                            CALENDAR ( _current_start_date, TODAY () ),
                            "weekday", WEEKDAY ( [Date] )
                        ),
                        NOT [weekday] IN { 6, 7 }
                    )
                ),
                0
            ),
        _current_satus = "Completed",
            IF (
                _current_start_date = BLANK (),
                IF (
                    _current_completed_date <> BLANK ()
                        && RELATED ( 'Date'[Date] ) <> BLANK ()
                        && RELATED ( 'Date'[Date] ) < _current_completed_date,
                    COUNTROWS (
                        FILTER (
                            ADDCOLUMNS (
                                CALENDAR ( RELATED ( 'Date'[Date] ), _current_completed_date ),
                                "weekday", WEEKDAY ( [Date] )
                            ),
                            NOT [weekday] IN { 6, 7 }
                        )
                    )
                ),
                IF (
                    _current_completed_date <> BLANK ()
                        && _current_start_date <> BLANK (),
                    COUNTROWS (
                        FILTER (
                            ADDCOLUMNS (
                                CALENDAR ( _current_start_date, _current_completed_date ),
                                "weekday", WEEKDAY ( [Date] )
                            ),
                            NOT [weekday] IN { 6, 7 }
                        )
                    )
                )
            )
    )

(3)Then we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi,  @kristel_tulio 

According to your description, an error is reported because your start date has data that is greater than the end date. Right?

Here are the steps you can follow：

(1)This is my test date:

(2)We can update the calculated column : "Count of Working days" 

Count of Working days =
VAR _current_start_date = 'Work Items'[StartDate]
VAR _current_completed_date = 'Work Items'[CompletedDate]
VAR _current_satus = 'Work Items'[Status]
RETURN
    SWITCH (
        TRUE (),
        _current_satus = "Not Started", 0,
        _current_satus = "Started",
            IF (
                _current_start_date <> BLANK (),
                IF (
                    _current_start_date < TODAY (),
                    COUNTROWS (
                        FILTER (
                            ADDCOLUMNS (
                                CALENDAR ( _current_start_date, TODAY () ),
                                "weekday", WEEKDAY ( [Date] )
                            ),
                            NOT [weekday] IN { 6, 7 }
                        )
                    ),
                    COUNTROWS (
                        FILTER (
                            ADDCOLUMNS (
                                CALENDAR ( TODAY (), _current_start_date ),
                                "weekday", WEEKDAY ( [Date] )
                            ),
                            NOT [weekday] IN { 6, 7 }
                        )
                    )
                )
            ),
        _current_satus = "Completed",
            IF (
                _current_start_date = BLANK (),
                IF (
                    _current_completed_date <> BLANK ()
                        && RELATED ( 'Date'[Date] ) <> BLANK (),
                    IF (
                        RELATED ( 'Date'[Date] ) < _current_completed_date,
                        COUNTROWS (
                            FILTER (
                                ADDCOLUMNS (
                                    CALENDAR ( RELATED ( 'Date'[Date] ), _current_completed_date ),
                                    "weekday", WEEKDAY ( [Date] )
                                ),
                                NOT [weekday] IN { 6, 7 }
                            )
                        ),
                        COUNTROWS (
                            FILTER (
                                ADDCOLUMNS (
                                    CALENDAR ( _current_completed_date, RELATED ( 'Date'[Date] ) ),
                                    "weekday", WEEKDAY ( [Date] )
                                ),
                                NOT [weekday] IN { 6, 7 }
                            )
                        )
                    )
                ),
                IF (
                    _current_completed_date <> BLANK ()
                        && _current_start_date <> BLANK (),
                    IF (
                        _current_start_date < _current_completed_date,
                        COUNTROWS (
                            FILTER (
                                ADDCOLUMNS (
                                    CALENDAR ( _current_start_date, _current_completed_date ),
                                    "weekday", WEEKDAY ( [Date] )
                                ),
                                NOT [weekday] IN { 6, 7 }
                            )
                        ),
                        COUNTROWS (
                            FILTER (
                                ADDCOLUMNS (
                                    CALENDAR ( _current_completed_date, _current_start_date ),
                                    "weekday", WEEKDAY ( [Date] )
                                ),
                                NOT [weekday] IN { 6, 7 }
                            )
                        )
                    )
                )
            )
    )

(3)This is my test result:

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kristel_tulio]

Hi @v-yueyunzh-msft 

I think it's working now. You're a great help. Thank you so much

[kristel_tulio]

Hi @v-yueyunzh-msft ,

It's the completed date that is less than the start date

[v-yueyunzh-msft]

Hi, @kristel_tulio 

Thanks for your reply, I modified my reply and glad to solve the problem for you.

[v-yueyunzh-msft · Accepted solution]

Hi, @kristel_tulio 

According to your description, You want to calculate the interval days (excluding weekends) according to the following conditions:

(1)[Status] = Not Started ，[Count of Working days]=0

(2) [status] = started, [Count of Working days] is the number of days between [StartDate] and today ().

(3) [status] = completed, if [StartDate] is blank (), then [Count of Working days] is the number of days between 'date' [date] and [CompletedDate]. Otherwise, [Count of Working days] is the number of days between [StartDate] and [CompletedDate]. Right?

Here are the steps you can follow：

(1)This is my test data:   ‘Work Items’ and  ‘Date’ table

(2)We can click “New Column” to create a  calculated column : “Count of Working days”

Count of Working days =
VAR _current_start_date = 'Work Items'[StartDate]
VAR _current_completed_date = 'Work Items'[CompletedDate]
VAR _current_satus = 'Work Items'[Status]
RETURN
    SWITCH (
        TRUE (),
        _current_satus = "Not Started", 0,
        _current_satus = "Started",
            IF (
                _current_start_date <> BLANK (),
                COUNTROWS (
                    FILTER (
                        ADDCOLUMNS (
                            CALENDAR ( _current_start_date, TODAY () ),
                            "weekday", WEEKDAY ( [Date] )
                        ),
                        NOT [weekday] IN { 6, 7 }
                    )
                ),
                0
            ),
        _current_satus = "Completed",
            IF (
                _current_start_date = BLANK (),
                IF (
                    _current_completed_date <> BLANK ()
                        && RELATED ( 'Date'[Date] ) <> BLANK ()
                        && RELATED ( 'Date'[Date] ) < _current_completed_date,
                    COUNTROWS (
                        FILTER (
                            ADDCOLUMNS (
                                CALENDAR ( RELATED ( 'Date'[Date] ), _current_completed_date ),
                                "weekday", WEEKDAY ( [Date] )
                            ),
                            NOT [weekday] IN { 6, 7 }
                        )
                    )
                ),
                IF (
                    _current_completed_date <> BLANK ()
                        && _current_start_date <> BLANK (),
                    COUNTROWS (
                        FILTER (
                            ADDCOLUMNS (
                                CALENDAR ( _current_start_date, _current_completed_date ),
                                "weekday", WEEKDAY ( [Date] )
                            ),
                            NOT [weekday] IN { 6, 7 }
                        )
                    )
                )
            )
    )

(3)Then we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kristel_tulio]

Hi @v-yueyunzh-msft 

Thanks for that, almost there. Sorry for the confusion. I'm just getting this error, it seems I do have startdate that is greater than completed date. Do you have any suggestion to show  how to tweak the calc. I will use it in reverse as Completed date - Start Date for some

[v-yueyunzh-msft]

Hi, @kristel_tulio 

According to your description,I'm not very clear about your [Status] judgment and the example data given, and the specific relationship between your 'Work Items' and 'Date' tables is not very clear. But according to your description, you want to get the interval between the two dates and remove the weekend. Right?

You can refer to the following steps:

(1)This is my test data:

(2)We can create a measure to calculate the days you need : “days”

days =
VAR _calendar_table =
    ADDCOLUMNS (
        CALENDAR (
            DATEVALUE ( SELECTEDVALUE ( 'Test'[StartDate] ) ),
            DATEVALUE ( SELECTEDVALUE ( 'Test'[CompleteDate] ) )
        ),
        "weekday", WEEKDAY ( [Date] )
    )
VAR _no_week =
    FILTER ( _calendar_table, NOT [weekday] IN { 6, 7 } )
RETURN
    COUNTROWS ( _no_week )

(3)We can put this measure and other fields in the visual to test , the result is as follows :

If this method can't help you solve the problem, can you provide your sample data in tabular form, and provide us with the output sample data you want in tabular form, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kristel_tulio]

Hi @v-yueyunzh-msft 

The calculation won't work on my end. I have blank values in both Startdate and Completed date. That's why I have to use the Date table to get the number of work days if there are blank values for start date and completed date. 

[kristel_tulio]

Hi @v-yueyunzh-msft 

Thank you for responding. Kindly refer to the sample below. I have two tables the Work Item and Date table and they both have relationship. I need to get the number of working days for each Work Item that are Completed, Started and Not Started. 

In my Work Item table I have the StartDate and CompletedDate. If the Work Item is tag completed calc will be difference of Started date and Completed date. if it is Started calc is StartDate less the current date. And Not Started is shown 0. But there are WorkItems that were tag "completed" but doesn't have startdate. I need to use the Date in date table instead if this is possible.

[kristel_tulio]

Hi @amitchandak 
this is my calculation

[amitchandak]

@kristel_tulio , where is calc?

You can use networkdays -https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c