Forum Discussion

Mesalome's avatar
Mesalome
Frequent Visitor
2 years ago

Creating for loop in measure

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. Whe...
Need Help
Tips and Tricks
AmiraBedh's avatar
AmiraBedh
Super User
2 years ago

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's avatar
Mesalome
Frequent Visitor
2 years ago

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.