Forum Discussion

Atanas_Atanasov's avatar
Atanas_Atanasov
Icon for Helper II rankHelper II
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/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

 

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

15 Replies

  • Atanas_Atanasov's avatar
    Atanas_Atanasov
    Icon for Helper II rankHelper II
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Atanas_Atanasov ,

       

      Could you please mark your solution? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

      Best Regards,
      Zhu
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Atanas_Atanasov , First create a calculated column for last appearance

     

    LastAppearanceDate =
    CALCULATE(
        MAX(PersonAppearances[AppearanceDate]),
        ALLEXCEPT(PersonAppearances, PersonAppearances[PersonID])
    )
     
    Then create a summary table to show distinct value
    SummaryTable =
    SUMMARIZE(
        PersonAppearances,
        PersonAppearances[LastAppearanceDate],
        "CountOfPersonID", DISTINCTCOUNT(PersonAppearances[PersonID])
    )
    • Atanas_Atanasov's avatar
      Atanas_Atanasov
      Icon for Helper II rankHelper II
      2 years ago

      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

       

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    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 >> 

     

    • Atanas_Atanasov's avatar
      Atanas_Atanasov
      Icon for Helper II rankHelper II
      2 years ago

      Thank you for your suggestion, but when added additional months of data, the dates in the column change to the last date in the dataset. I found the solution and will share it below.