Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Name | Group | Startdate | Enddate |
A | 1 | 01.01.21 | 23.03.21 |
B | 2 | 01.02.21 | 09.10.21 |
C | 3 | 01.04.21 | 20.12.21 |
D | 4 | 02.05.21 | 06.09.21 |
E | 1 | 13.02.21 | 12.04.21 |
F | 2 | 02.06.21 | 23.07.21 |
G | 3 | 03.04.21 | 28.10.21 |
H | 4 | 05.07.21 | 04.09.21 |
I | 1 | 12.06.21 | 13.11.21 |
J | 2 | 01.01.21 | 23.08.21 |
K | 3 | 01.02.21 | 09.10.21 |
L | 4 | 01.04.21 | 20.12.21 |
M | 1 | 02.05.21 | 06.09.21 |
Datetable is simple Calendarauto. No relationship between the two tables.
The (not correctly working) calculated colum was
Solved! Go to Solution.
@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]) )
))
@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]) )
))
Great! Works like magic! Thank you!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
63 | |
53 | |
39 | |
25 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |