topic Re: For looping in DAX to calculate starting dates according to end dates of tasks in DAX Commands and Tips

For looping in DAX to calculate starting dates according to end dates of tasks

Anonymous — Fri, 10 Sep 2021 14:38:04 GMT

Hello community,

I'm trying to build a GANTT view in my Power BI Report using tasks durations and prerequisites. I need to calculate the starting date of an operation according to the end date of its prerequisites (that is not usually unique). You can find below the content of my table:

I set up a starting date of the first task to 1 september using the DAX code :

start = IF(Jointure[bool]=="vrai";[debutprojet])

Then I calculated the end date using this formula :

end = Jointure[start]+(Jointure[duree_operation]/8)

I tried the following formula for calculating the remaining start dates :

start = IF(Jointure[bool]=="vrai";[debutprojet];LOOKUPVALUE(Jointure[end];Jointure[operationID];Jointure[prerequisID]))

Unfortunatly, a circular dependency wad detected.

How can I loop 2 times through operationsID and prerequisites to find at each iteration the maximum value of an operation prerequisites end date ?

Thanks for your help !

Re: For looping in DAX to calculate starting dates according to end dates of tasks

Greg_Deckler — Fri, 10 Sep 2021 14:49:59 GMT

Anonymous Well, you can do emulated FOR and WHILE loops in DAX.

https://community.powerbi.com/t5/Quick-Measures-Gallery/For-Loop/m-p/637531#M319

https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320

Recursion or previous value is very difficult

https://community.powerbi.com/t5/Quick-Measures-Gallery/Previous-Value-aka-quot-Recursion-quot/m-p/637614#M321

However, I'm not sure exactly what you want. Can you post sample data as text and expected output?

Re: For looping in DAX to calculate starting dates according to end dates of tasks

Anonymous — Mon, 13 Sep 2021 11:34:14 GMT

Please refer to the picture above:

Operation1 doesn't have any prerequisite so it should take the starting date of the project that I've introduced as a measure.

Operation 2 has one prerequisite (which is operation 1) should start at the end of operation 1. It ends at end of operation 1 +duration of operation 2

Same thing for operation 3 it follows operation 2 so it shoul start at the end date of operation 2 and it will finish 3 days after because the execution of operation takes 21 hours (8 working hours a day)

....

operation 11 has two prerequisites 9 and 10, so its starting date will be the max finish date of 9 and 10.