calculate number of working days between 2 dates and set 2 conditional formating

[Lihobday]

I am seeking help on powerbi! I had been figuring this out for weeks already!! Have 2 requirements... I have 2 dates. First is "assigned date" and second is "actual briefing date".

1. How may i calculate by working days the difference between actual brefing date and assigned date. Sometimes assigned date field is left blank and i will still need to calculate the difference of actual briefing date till today's date.

2. If the working days calculated is 10 days to 19 days old, can the work days be reflected as yellow. If more than 20 days, can the calculated work days be reflected at red?

Really need some experts advice here... thank you

[Anonymous · Accepted solution]

Hi @Lihobday,

Smoupre's solution seems well, you can also take a look at below formula, I add the condtion to replace blank value to current date.

WorkDay Count =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALENDAR (
                [Assigned date],
                IF ( [Actual briefing date] <> BLANK (), [Actual briefing date], TODAY () )
            ),
            "DayofWeek", WEEKDAY ( [Date], 1 )
        ),
        [DayofWeek] <> 1
            && [DayofWeek] <> 7
    )
)

Original table:

Result:

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @Lihobday,

Smoupre's solution seems well, you can also take a look at below formula, I add the condtion to replace blank value to current date.

WorkDay Count =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALENDAR (
                [Assigned date],
                IF ( [Actual briefing date] <> BLANK (), [Actual briefing date], TODAY () )
            ),
            "DayofWeek", WEEKDAY ( [Date], 1 )
        ),
        [DayofWeek] <> 1
            && [DayofWeek] <> 7
    )
)

Original table:

Result:

Regards,

Xiaoxin Sheng

[Lihobday]

Thank you for the solution XiaoXin Sheng! This work wonders!!! Had been figuring this out for a long time...!

[Anonymous]

Hi @Lihobday,

I'm glad to hear that my formula is helps for you.

Regards,

Xiaoxin Sheng

[Lihobday]

Hi v-shex,

I have another problem that require some help. 

1. I have another column Close Date that has been added. How may i calculate by working days the difference between actual Close date and Brief date. The challenge is sometimes brief date field will be left blank. And i will get error from POWERBI "The start date or end date in Calendar Function can not be Blank value"

Is it possible to have a fomulae that calculates only if there is an entry in "Brief date" else it will not calculate? 

Thank you!!

[Anonymous · Accepted solution]

Hi @Lihobday,

If 'brief date' and 'actual close date' both not blank, which one you'd like to calculate?

In my opinion, I will choose the smaller one as the end date parameter:

WorkDay Count =
VAR closedate =
    MIN ( [Actual close date], [Actual briefing date] )
RETURN
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                CALENDAR ( [Assigned date], IF ( closedate <> BLANK (), closedate, TODAY () ) ),
                "DayofWeek", WEEKDAY ( [Date], 1 )
            ),
            [DayofWeek] <> 1
                && [DayofWeek] <> 7
        )
    )

Notice: min and max function will ignore blank value.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Create a custom column in your data table:

Calc Date = IF(ISBLANK([AssignedDate]),TODAY(),[AssignedDate])

Use that to calculate working days. 

Use conditional formatting in a table visualization to show yellow and red values.

[Lihobday]

Thank you Smoupre!