Need help with an overview of remaining holidays

[flinke]

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-11	2016-12	Remaining Days in 2016	2017-01	2017-02	…	2017-11	2017-12	Remaining Days in 2017	2018-01	2018-02	…	2018-12	Remaining Days in 2018
Employee A		10	3	3	20	20		17	-2	1	20	18		13	14

[v-huizhn-msft]

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