Forum Discussion

flinke's avatar
flinke
New Member
8 years ago
Solved

How do I display remaining holidays in a table?

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-02…2017-112017-12Remaining Days in 20172018-012018-02…2018-12Remaining Days in 2018
Employee A 10332020 17-212018 1314
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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