Forum Discussion
Counting Value Based On It's Last Appearence
- 2 years ago
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
SachinNandanwar - Can you please show it as a count of the people? Thank you very much for your help!
Atanas_Atanasov
If there is one unique entry of a person across a given date the count would always be one for that given date.
- Atanas_Atanasov2 years ago
Helper II
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
- SachinNandanwar2 years ago
Impactful Individual
Could you please share some sample data ?
- Atanas_Atanasov2 years ago
Helper II
That's my dataset for January. The only think you have to do is create a calendar and you will have the same datamodel. The relationship is based on calednar.date -> table.MetricDate
MetricDate App Value Refresh date PersonId 1/1/2024 Word 918030 7/8/2024 032d88a7-7ea7-3195-829c-383e976f6cf3 1/2/2024 Word 933257 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/3/2024 Word 401560 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/4/2024 Word 37782 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/5/2024 Word 934145 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/6/2024 Word 625459 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/7/2024 Word 166553 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/8/2024 Word 210070 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/9/2024 Word 524790 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/10/2024 Word 218922 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/11/2024 Word 867779 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/12/2024 Word 281966 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/13/2024 Word 719946 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/14/2024 Word 518357 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/15/2024 Word 355841 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/16/2024 Word 155917 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/17/2024 Word 577269 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/18/2024 Word 154694 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/19/2024 Word 90236 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/20/2024 Word 541938 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/21/2024 Word 390435 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/22/2024 Word 536700 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/23/2024 Word 436265 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/24/2024 Word 297394 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/25/2024 Word 963107 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3 1/26/2024 Word 671828 7/8/2024 031d88a7-7ea7-3195-829c-383e976f6cf3