Forum Discussion
Counting Unique Values Level based on last timestamp
Hi some_bih
In my actual data is a timestamp dd/mm/yyyy hh:mm:ss.
In the sample I meant to have dd/mm/yyyy, but from what I see now the I have 1 not valid date for CustomerID 1111 as 10/18/2023 instead of 18/10/2023. The rest are ok.
For Level 4 is 0 because on the last entry(timestamp) of customerID 4444 he is on Level 5.
I believe my main issue here is that not the actual count, but how to make make a summarize table with only the last date for each customer ID as follows :
| CustomerID | Timestamp | Level |
| 1111 | 18/10/2023 | Level 2 |
| 2222 | 01/04/2023 | Level 1 |
| 3333 | 05/08/2023 | Level 3 |
| 4444 | 03/05/2023 | Level 5 |
| 5555 | 05/07/2023 | Level 2 |
If I have this table (as a temporary table in a measure), the count part is simple.
Also thank you, I think you gave me an idea now...
If I put the maxdate in the summary table then I might be able to work out the max/latest level per customer....
Hi aabi please create another column to check true false last date for customer
Did I answer your question? Kudos appreciated / accept solution!
Output
Hi aabi
If this is solution, please accept it so other member of community could use it. Thank you
Hi some_bih
While what you made kind of works, but is not something that I can use on my model.
However with your assistance/inspiration, I have managed to create the formula that I need as follows :
Level 1 Count = VAR LatestTimestampPerCustomer =
ADDCOLUMNS(
SUMMARIZE(
'Table1',
'Table1'[CustomerID],
"MaxTimestamp", MAX('Table1'[Timestamp])
),
"LatestLevel",
CALCULATE(
MAX('Table1'[Level]),
FILTER(
'Table1',
'Table1'[CustomerID] = EARLIER('Table1'[CustomerID]) &&
'Table1'[Timestamp] = EARLIER([MaxTimestamp])
)
)
)
RETURN
COUNTROWS(
FILTER(
LatestTimestampPerCustomer,
[LatestLevel] = "Level 1"
)
)I just need to change the "Level 1" to "Level 2" on the countrows for each level. Results will be as follows :
Level 1 Level 2 Level 3 Level 4 Level 5 1 2 1 0 1