Forum Discussion
Creating for loop in measure
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?
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?Give me a moment, your case is a tricky. Did you think about a model ?
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?
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.
