Forum Discussion
Working Day calculation with condition
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
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' ) ))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
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' ) ))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
