Forum Discussion

Atanas_Atanasov's avatar
2 years ago
Solved

Counting Value Based On It's Last Appearence

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/...
  • Atanas_Atanasov's avatar
    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