cancel
Showing results for
Did you mean:
Helper II

## 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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
5 REPLIES 5
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Microsoft

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?

Proud to be a Datanaut!

Helper II

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 ?

Microsoft

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)

Proud to be a Datanaut!

Helper II

"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 ?

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!