Forum Discussion

usman96's avatar
usman96
Icon for Helper II rankHelper II
8 years ago
Solved

Remaining Days of Month Calculation

I have this column which have dates  , these dates show the day on which employees are "present" How to calculate absent days (other days which are not in this column) ? in powerbi   
Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi usman96,

     

    You can use below calculate column to achieve your requirement:

    present =
COUNTROWS (
    FILTER (
        ALL ( list ),
        YEAR ( [Date] ) = YEAR ( EARLIER ( list[Date] ) )
            && MONTH ( [Date] ) = MONTH ( EARLIER ( list[Date] ) )
    )
)

absent = 
DAY ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ) + 1, 1 ) - 1 )
    - [present]

    Result:

     

    Regards,
    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi usman96,

 

You can use below calculate column to achieve your requirement:

present =
COUNTROWS (
    FILTER (
        ALL ( list ),
        YEAR ( [Date] ) = YEAR ( EARLIER ( list[Date] ) )
            && MONTH ( [Date] ) = MONTH ( EARLIER ( list[Date] ) )
    )
)

absent = 
DAY ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ) + 1, 1 ) - 1 )
    - [present]

Result:

 

Regards,
Xiaoxin Sheng