Creating for loop in measure

[Mesalome]

For each employee max vacation length is 24 days which accrues in a year (365 days) and they stop adding up if an employee don't use them when it reaches 24. 

Here is the sample data in Excel. Where we can calculate how many days accrued between vacations in this way (I was able to do this in Power BI as well)

D3 = MIN(24, DATEDIF(C2,C3,"D")*24/365)
D4 = MIN(24, DATEDIF(C3,C4,"D")*24/365)

And Balance After Vacation is calculated like this:

E3 = MIN(E2+D3, 24)-B3
E4 = MIN(E3+D4, 24)-B4

This shows that to calculate value in Balance After Vacation column I need previous value from the same column which isn't possible in the Power BI (I think cause I wasn't able to do it). 


Thus to solve this and caluclate Balance After Vacation, I  was able to find that there is a possibility to create for loop logic using DAX code and  I'm trying to apply same approach to my case: To calculate each Balance After Vacation I want to create for loop and caluclate MIN(Balance Accrued Between Vacations + Previous Balance After Vacation, 24) - vacation.Amount for all the previous rows
but I can't get the results I'm aming for.  

Here is the table I have in Power Bi 

and a measure I created to calculate Balance After Vacation [Vacation Balance]

Vacation Balance = 
VAR __userGUID = MIN(employees_vacations[guid])
VAR __vacationNumber = MIN(employees_vacations[Index])

-- Generate a "loop table" for each vacation number
VAR __loopTable = 
    GENERATESERIES(1, __vacationNumber)

-- Initialize the vacation balance to 0
VAR __vacationBalance = 0

-- Calculate Vacation Balance for each vacation number
VAR __vacationBalanceTable =
    ADDCOLUMNS(
        __loopTable,
        "__Vacation Balance",
        VAR __currentVacationNumber = [Value]
        VAR __vacationAmount = 
            SUMX(
                FILTER(
                    employees_vacations,
                    employees_vacations[guid] = __userGUID &&
                    employees_vacations[Index] = __currentVacationNumber &&
                    employees_vacations[vacation.Leave type] = "Vacation"
                ),
                employees_vacations[vacation.Amount]
            )
        VAR __balanceAccrued = 
            SUMX(
                FILTER(
                    employees_vacations,
                    employees_vacations[guid] = __userGUID &&
                    employees_vacations[Index] <= __currentVacationNumber
                ),
                employees_vacations[Balance Accrued Between Vacation]
            )
        RETURN
            // Update the vacation balance with the accrued balance and subtract the vacation amount
            // Ensure that the vacation balance doesn't exceed 24
            MIN(__vacationBalance + __balanceAccrued, 24) - __vacationAmount
    )

-- Calculate the final Vacation Balance by selecting the last value
VAR __finalVacationBalance = MAXX(__vacationBalanceTable, [__Vacation Balance])

RETURN
    __finalVacationBalance

Here is link to my Power BI project with Sample Data. 
https://drive.google.com/file/d/1uOUQ6qdnrCMmrWND_0SDtgU9mHx6Vd_J/view?usp=sharing

I'm fairly new with Power BI and don't know much and this one is real head scratcher for me and I really want to find a way to solve this. I'm open to suggestions, if there is other easier ways to solve this. 

P.S. summing up vacation.Amount and Balance Accrued Between Vacations and subtracting them isn't an option because each time we need to ake sure that if the sum of previous vacation balance and accrued vacations go over 24, employee has only 24 days. 

Thank you in advance.

[AmiraBedh]

Your case is interesting ! But I am a little bit lost here ! Can you give an example for an exmployee clearer than the one you provided?

[Mesalome]

Thank you for your interest! I'm happy to clear things up, if my response will leave you with more questions please feel free to ask me.

When this user was hired (8-Nov_2021) vacation days started accruing.

When she took her first vacation (Index = 1) which lasted 8 days she had 18.87 days accrued thus balance after vacation was 10.87 days.

From first vacation to second one, she accrued 23.47 days, but she had 10.87 days left from previous vacation, company's policy indicates that you can't have more than 24 days accrued, So when she took second vacation (Index = 2) she had MIN(24, 23.47+10.87) vacation days which is 24 and vacation lasted for 10 days thus she was left with 14 vacation days on her balance.

From second to third vacation she accrued 7.56 days. Third Vacation lasted 4 days (Index=3) and after this she was left with MIN(24, 7.56+14) - 4 = 17.56 vaction days on her balance.

and it goes on, I want to have vacation balance for today for each employee, so we can inform employees if they are near 24 days or already have 24 days and aren't getting vacation days anymore to take their well deserved vacations. 

If I can manage to count balance after final vacation, which is 12.88 in this case, it will be easy to calculate vacation balance for today.

Problem is that for each vacation I need to know previous vacation balance to determine how many days they have left after this vacation on their balance.

Does this make things a bit clearer?

[AmiraBedh]

When she took her first vacation (Index = 1) which lasted 8 days she had 18.87 days accrued thus balance after vacation was 10.87 days.

which column gives us what she had ? or at least the initial number of holidays?

[Mesalome]

In power bi?
I have [vacation.Start date] and [Previous Vacation Start Date] columns which generates dates between them and *24/365 is vacations that accrued between those dates (can't be more than 24). 
If [Previous Vacation Start Date] is null this means that it's employee's first vacation, thus we need to calculate between first vacation start date and hiring date. Which is the case when Index = 1. 
Initial vacation days is 0 at the day they are hired and then it adds up every day.

Balance Accrued Between Vacation = 
VAR CurrentEmployeeGUID = employees_vacations[guid]
VAR CurrentStartDate = employees_vacations[vacation.Start date]
VAR EmployeeVacations = 
    SUMMARIZE (
        FILTER (
            employees_vacations,
            employees_vacations[guid] = CurrentEmployeeGUID &&
            employees_vacations[vacation.Start date] = CurrentStartDate &&
            employees_vacations[vacation.Leave type] = "Vacation"
        ),
        employees_vacations[guid],
        "Balance Accrued Between Vacation",
        MIN (
            24,
            DATEDIFF (
                COALESCE ( MAX(employees_vacations[Previous Vacation Start Date]), employees_vacations[hire_date] ),
                MAX(employees_vacations[vacation.Start date]),
                DAY
            ) * 24 / 365
        )
    )
RETURN
    MAXX(EmployeeVacations, [Balance Accrued Between Vacation])

I have filters in the calculated column which doesn't make sense in sample data but in actual data I have different types of leaves and this calculation is only for "Vacation" types, plus I have many users with different GUID's that's why I have those filters. You can ignore or delete them. 

[AmiraBedh]

Give me a moment, your case is a tricky. Did you think about a model ?