Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
OliTFD
Regular Visitor

How to calculate the average count of rows? Persons with dif. start and end dates in dif. groups

I have a table with different persons, all with different start and end dates (of employment).
I want to calculate the count of them for eyery time. But not only for every day, I also need to find the average for time periods month and quarter.
The persons belong to different groups. Here is where I get lost:
If I calculate countrows in a calculated column of the datetable, filtering on the groups is not applied to this calculation.
So any following calculation like average is not influenced by the group. Even filtering with something like
Persons[Group]=SELECTEDVALUE(Persons[Group]) does not work, because measures can not be used inside calculated colums, as I learned.
Any idea how to solve this?

The table is simple like

NameGroupStartdateEnddate
A101.01.2123.03.21
B201.02.2109.10.21
C301.04.2120.12.21
D402.05.2106.09.21
E113.02.2112.04.21
F202.06.2123.07.21
G303.04.2128.10.21
H405.07.2104.09.21
I112.06.2113.11.21
J201.01.2123.08.21
K301.02.2109.10.21
L401.04.2120.12.21
M102.05.2106.09.21


Datetable is simple Calendarauto. No relationship between the two tables.
The (not correctly working) calculated colum was

Count of active Persons per Day =
CALCULATE(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date])),
FILTER(Persons,Persons[Enddate]>=(Days[Date])),
FILTER(Persons,Persons[Group]=SELECTEDVALUE(Persons[Group]))
)
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@OliTFD , Try like

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) && Persons[Group]=SELECTEDVALUE(Persons[Group]))
))

 

or

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) )
))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@OliTFD , Try like

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) && Persons[Group]=SELECTEDVALUE(Persons[Group]))
))

 

or

 

averageX(values(Days[Date]), calculate(COUNTROWS(Persons),
FILTER(Persons,Persons[Startdate]<=(Days[Date]) && Persons[Enddate]>=(Days[Date]) )
))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Great! Works like magic! Thank you!

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.