Calculate the total weekdays of all task of a project using Flow

[Prajnya]

Hi Team, 

I am developing a flow in which I have tried to bring all tasks of a Project and calculate the total weekdays of all the Task. 

These are below the screenshot of my flow

Error : 

Please help me find the solution. Thanks

[DamoBird365]

Hi @Prajnya 

The error looks like you are trying to run the expression adddays on a list of dates rather than a single date.

I thought I would have a go at this problem you have myself.

I have initialised a variable for the initial date (so that I can update it), a compose for the end date and a empty array for a list of days between both dates.  I then use a until loop and add all dates between start and finish to an array.  I then perform a filter on the array where the dayofweek expression is less than 5 (day of week starts from 0) and calculate the length of the filtered array to return the number of week days.

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

P.S. take a look at my new blog here

[DamoBird365]

Hi @Prajnya 

After a chat with @Paulie78 i've realised that i've overcomplicated my first solution provided to you.  I am a lover of the select action and should have probably realised there would be a more efficient way of doing things. So I have included the following as a comparison to the first solution provided so that users can see where efficiencies can be had.

You can calculate the number of days between two dates and then create an array of dates with a select and range expression.  You can then filter that array as before where the day of week is greater than 0 and less that 6 and then use length for the number of working days.  Per my above post, I also hadn't realised that dayofweek starts on a Sunday 0 and ends on a Saturday 6, so greater than 0 i.e. 1 and less than 6 i.e. 5 will return Monday to Friday.

@and(less(dayOfWeek(item()), 6),greater(dayOfWeek(item()), 0))

This solution runs in 0s vs the original until loop which takes 10-15 seconds to create the array of dates.

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

P.S. take a look at my new blog here

[Prajnya]

Hi @DamoBird365 

Thank you for your quick response

[Prajnya]

Hi @DamoBird365 

The below is my requirement for which I have developed the flow and getting the error. 

Please help me in the logic. Thanks in advance 

Step1: Get all Projects from the Project list

Step2: Get all tasks from the Task list where Project Id is lookup in Task List

Step3: Count no of task for a Project

Step4: Sum of workdays all tasks = Total no of workdays in Project

[DamoBird365]

Hi @Prajnya 

I understand that these project lists are SharePoint lists?

Once you have achieved step 1 & 2 (get items), you will need to put the value of step 1 (all projects) into an apply to each.

Within this apply to each you can apply a filter array on all tasks where the project id's are equal and then use the expression length() in order to work out the number of tasks for each project.

Working out the number of workdays for ALL tasks related to a project is a bit more difficult as there is no native summing function.  I have covered a summing concept here

Nesting apply to each actions is also something to try and avoid as it reeks havoc on performance.

So, how do we calculate the number of working days?  If we start from the beginning, one option might be to create a new array with the no of week days in the array.  You could create a seperate apply to each on the projects and perform the calculation.  Once this has been completed, you could include an additional filter within the apply to each to obtain projects with the same project ID and then preform the summing concept above.

Your solutions would look like follows:

Get Projects

Get Tasks

Apply To Each on Tasks

     Create Array on Each Task to calculate number of Working Days

Apply to Each on Projects

     Filter Each Tasks (where Project ID = Current Project ID)

     Compose with expression Length(of above filter) 

     Filter New Working Days Array (where Project ID = Current Project ID)

     Perform Sum on the Days Returned in above Filter (you can use an apply to each if need be but my link on Sums could help)

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

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Prajnya]

Thanks @DamoBird365 

I will work on this and will get back to you ASAP

[Prajnya]

Hi @DamoBird365 

Please find the attachment below and guide me for the length expression to achieve the no of task in each Project. 

[DamoBird365]

Hi @Prajnya 

You use the expression Length() and choose the dynamic value to return the length of array:

I note you have an apply to each within an apply to each.  What is the purpose for this?  What two values are you traversing through?

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

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉