How do I display remaining holidays in a table?

[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-yulgu-msft · Accepted solution]

Hi @flinke,

Please try below formulas:

Remaining days in Month12 = 
IF (
    'Holiday Remain'[YearMonth].[MonthNo] = 12,
    CALCULATE (
        SUM ( 'Holiday Remain'[Remaining holidays] ),
        FILTER (
            ALLEXCEPT ( 'Holiday Remain', 'Holiday Remain'[Employee] ),
            'Holiday Remain'[YearMonth].[Year]
                = EARLIER ( 'Holiday Remain'[YearMonth].[Year] )
                && 'Holiday Remain'[YearMonth].[MonthNo] = 12
        )
    ),
    BLANK ()
)

remaining days per year = 
CALCULATE (
    SUM ( 'Holiday Remain'[Remaining days in Month12] ),
    FILTER (
        ALLEXCEPT ( 'Holiday Remain', 'Holiday Remain'[Employee] ),
        'Holiday Remain'[YearMonth].[Year]
            <= EARLIER ( 'Holiday Remain'[YearMonth].[Year] )
            && 'Holiday Remain'[YearMonth].[MonthNo]
                <= EARLIER ( 'Holiday Remain'[YearMonth].[MonthNo] )
    )
)

Best regards,

Yuliana Gu