Power Bi loop for calculating end date based on number of hours estimated but counting weekends, holidays and the persons absence

In DAX (power bi) how would the code be like for calculating the end date of a task based on estimated time for completion...

So to start date, add estimated time divides by number of hours the person will spend per day working on the task to get the end date that doesnt include work free days.

Then check if there are any absences, weekends or public holidays in the period (we have separate tables with those work free dates). If there are than add the number of these work free days to the end date we calculated before.

Than check again and again till there are no new weekends, absences or holidays in the period.

I think i should do this with a loop but don't know exactly how.

I just have the problem with making a loop that would check work free days till there weren't any few yet to really get the end date of a task for that employee (jira epic).

I did the part that checks if there are absences, holidays or weekends and to add the number to the date before counting work free days (estimatedEnd Date) and it looks like this:

End Date =

VAR HoursWorkingPerDayValue = HoursWorkingPerDay[HoursWorkingPerDay Value] // how many hours people work per day

VAR EstimatedEndDate = TODAY() + CEILING([Remaining Effort Sum (h)] / HoursWorkingPerDayValue, 1) + 0 //? // When epic(s) would be done if person has no absense planned

VAR PersonAbsence = CALCULATE(SUM('GAM Absence'[Reported]), KEEPFILTERS('SHARED CalendarDay'[Date] >= TODAY() && 'SHARED CalendarDay'[Date] <= EstimatedEndDate))

CALCULATE(SUM('GAM AbsenceRequest'[Reported]), KEEPFILTERS('SHARED CalendarDay'[Date] >= TODAY() && 'SHARED CalendarDay'[Date] <= EstimatedEndDate)) + 0 //how many hours between today and estimatedEndDate ?

VAR EndBeforeCountingWeekendsHolidays = EstimatedEndDate + CEILING(PersonAbsence/HoursWorkingPerDayValue, 1) + 0

VAR Weekends = CALCULATE(COUNTROWS('SHARED CalendarDay'), KEEPFILTERS('SHARED CalendarDay'[Weekday] >= 6 && 'SHARED CalendarDay'[Date] >= TODAY() && 'SHARED CalendarDay'[Date] <=EstimatedEndDate ))

// calculate the total number of work-free days for all countries

VAR TotalWorkFreeDays = Weekends + [SLOholidays]

VAR FinalEndDate = EndBeforeCountingWeekendsHolidays + TotalWorkFreeDays + 0

RETURN FinalEndDate

Solution

You can't do loops in DAX, but you can approach your solution in a different way.

So assuming that you already know how to calculate how many days it takes to finish the task:

VAR DaysToComplete = CEILING([Remaining Effort Sum (h)] / HoursWorkingPerDayValue, 1) + 0

And you have a table with the dates, one way would be to start with your shared calendar, and remove the dates where the person is absent:

VAR datesTable = //Here from the shared calendar, remove the weekends and absence days, for example:
CALCULATETABLE('SHARED CalendarDay',
'SHARED CalendarDay'[Date] >= TODAY(),
'SHARED CalendarDay'[Weekday] < 6,
EXCEPT(VALUES('SHARED CalendarDay'[Date]);VALUES('GAM Absence'[Date])))

Then you can use the RANKX function to give a number to each date. The nth value is the finish date you need:

VAR datesTable = //Here from the shared calendar, remove the weekends and absence days, for example:

ADDCOLUMNS(CALCULATETABLE('SHARED CalendarDay',
       'SHARED CalendarDay'[Date] >= TODAY(),
       'SHARED CalendarDay'[Weekday] < 6,
       EXCEPT(VALUES('SHARED CalendarDay'[Date]);VALUES('GAM Absence'[Date]))),
   "rankDays", RANKX ( ALLSELECTED ( 'SHARED CalendarDay'[Date] ), 'SHARED CalendarDay'[Date],,ASC, DENSE ), 
   "selectedDay", LASTDATE('SHARED CalendarDay'[Date]))

So with this rank column, your end date is the Nth record on the table and can be retrieved like this:

RETURN CALCULATE(MAXX(datesTable , IF([rankDays] = DaysToComplete, [selectedDay], BLANK())))

This formula will need to be tweaked to your specific model, but hopefully you get the gist of what I'm suggesting.