cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
flinke Frequent Visitor
Frequent Visitor

Need help with an overview of remaining holidays

Hello,

I hope you can help me - thanks in advance. I want to create a table of how many remaining holidays an employee has for that specific month with an extra field for the year that accounts the remaining holidays for the year by also considering the previous years. If you look at the table below you can see that there have been 3 days left for 2016. Though while having -2 days in total for 2017 in December 2017 the final result for 2017 will be 1 (considering the 3 days of 2016 --> 3-2=1). Same goes for 2018: the employee has 13 days left and 1 day remaining from 2017; therefore he will have a result of 14 days in 2018. The numbers in the month fields represent the amount of holidays left at that specific point in time for that year.

 

 2016-112016-12Remaining Days in 2016 2017-012017-022017-112017-12Remaining Days in 20172018-012018-022018-12Remaining Days in 2018
Employee A 10332020 17-212018 1314
1 REPLY 1
v-huizhn-msft Super Contributor
Super Contributor

Re: Need help with an overview of remaining holidays

Hi @flinke,

I assume your source table have the Month, Year, left holidays columns. Please create a measure using the formula below.

Remaining Days =
VAR Year = Table1[Year]
RETURN
    CALCULATE (
        SUM ( Table1[left holidays] ),
        FILTER ( Table1, Table1[Year] <= Year && ( Table1[Month] ) = 12 )
    )


Best Regards,
Angelia