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 ...
  • 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

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