Dear All,
I have a report which contains person ID.
I'd like to calculate the distinct count of that person ID, based on their last appearence.
For example I have 1 person who appears only on 01/01/2024 and another person who appears on all other days, but their last appearence is 01/26/2024 (screenshot 1).
I'd like to show a table which has date and the distinct count next to it and it should look like that:
Date: Count Of Person ID
01/01/2024 1
01/26/2024 1
NOTE: ID2 appears in multiple days, but I am looking for is to show only their last date of appearence in the report.
I will be deeply grateful if you can help me with that.
NOTE: This is how my datamodel looks (screenshot 2)
All due respect,
Atanas
Ok, I found the solution.
1. Create table by Person ID, Calendar Date and the distinct count of the person ID. Then removing the filter context from the calendar date and the sorting column in the calendar table:
2. Grouped the table from step 1 by person id, while interating the group to get the last date (MAXX). And then selecting only the column with the latest date:
3. Then if the calendar date = the date found in step 2 return me the distinct count of the person id where the calendar date = last calendar date:
The result is exactly what I was looking for:
Thank you all for your help and suggestions. Hope this might help to you as well!
Have a nice day and great weekend,
Atanas
Ok, I found the solution.
1. Create table by Person ID, Calendar Date and the distinct count of the person ID. Then removing the filter context from the calendar date and the sorting column in the calendar table:
2. Grouped the table from step 1 by person id, while interating the group to get the last date (MAXX). And then selecting only the column with the latest date:
3. Then if the calendar date = the date found in step 2 return me the distinct count of the person id where the calendar date = last calendar date:
The result is exactly what I was looking for:
Thank you all for your help and suggestions. Hope this might help to you as well!
Have a nice day and great weekend,
Atanas
Is it possible that there will be multiple entries of a person across the same dates ?
If not then just use this Measure
MaxDatePresence = CALCULATE(MAX(person[date].[Date]),INDEX(1,DISTINCT(ALL(person)),ORDERBY(person[date].[Date],DESC),PARTITIONBY(person[person])))
Source Data >>
date,personid
1/8/2024,1
2/8/2024,1
5/8/2024,1
8/8/2024,1
11/8/2024,1
3/8/2024,2
4/8/2024,2
10/8/2024,2
o/p >>
Thank you again, but the result this measure provides is the last date in my dataset (screenshot 1). Except for line 1.
I think this is not working due to the fact I have calendar table in my data model (screenshot 2) from my original post, but not sure how to resolve it,
Atanas
@Atanas_Atanasov , First create a calculated column for last appearance
Proud to be a Super User! | 
|
@bhanu_gautam - Thank you for the suggestion, but is it possible to do it with a measure?
Additinionaly, when I add more months of data the date in the column you suggest automatically changes to the latest in the dataset. While I need this to be calculated for the month in the filter.
Thank you,
Atanas
Helpful resources
FabCon Global Hackathon
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
