Working Day calculation with condition

[Anonymous]

Hi There,

I hope you all must be doing great.

I need help in the calculation of the working days between ‘’Planned Planning’’ and ‘’Actual Planning’’ with the condition if the value in ‘’ Delay Planning’’ is more than Zero.

I tried the below formula but don’t know how to add weekend

DelayWorkingDays = if('All Projects Query'[Delay planning]>0, 'AllProjects Query'[Actual Planning] - 'All Projects Query'[Planned Planning])*1

 Regards,

Zaid

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

A solution to this scenario requires a date table.

Based on the new date table, create a calculation column to determine whether it is a working day.

is_workday = NOT(WEEKDAY('Table'[Date])in {1,7})

Using the is_workday column, the original table can now include a new calculated column which writes as follows:

DelayWorkingDays = IF('All Projects Query'[Delay Planning]>0,
CALCULATE(
    COUNTROWS ( 'Table'),
    DATESBETWEEN ('Table'[Date],  'All Projects Query'[Planned planning], 'All Projects Query'[Actual Planning]-1 ),
    'Table'[is_workday] = TRUE,
    ALL ( 'All Projects Query' )
))

Sample .pbix

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

[Anonymous]

Thanks a lot, it really helped

[Greg_Deckler]

Net Work Days? https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

[Anonymous]

I am not able to understand it, can help me to understand it