Remaining Days of Month Calculation

[usman96]

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 

[Anonymous · Accepted solution]

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

[Phil_Seamark]

HI @usman96

You could join to a Date table and add two columns.  The first column showing a 1 on dates that match your list.  The second column shows a 1 on days where there is no match.

Does that sound like it might work for you?

[usman96]

it would be better if could show them in bar dual bar chart 

The first bar show the present days  in number like ( 7 present days)

and the 2nd chart show the absent days  in numbers like ( 2 absent days ) 

Can you show me how to achieve this ? 

[Phil_Seamark]

HI @usman96

Can you please mock something up (using your sample data) as I'm still not 100% clear.

I'm sure it's possible to come up with a formula, I just don't understand what you are trying to do (Sorry)

[usman96]

"You could join to a Date table and add two columns.  The first column showing a 1 on dates that match your list.  The second column shows a 1 on days where there is no match."

Can you implement this and give me some sample of what you said ?