Counting Unique Values Level based on last timestamp

[aabi]

Hello All

I have been trying but with no effect to count a the numbers of users that are on a certain level. But only the MAX of a certain time stamp

As a sample the table below

CustomerID	Timestamp	Level
1111	1/1/2023	Level 1
1111	1/3/2023	Level 1
1111	10/18/2023	Level 2
2222	1/2/2023	Level 1
2222	1/3/2023	Level 1
2222	1/4/2023	Level 1
3333	5/6/2023	Level 1
3333	5/7/2023	Level 2
3333	5/8/2023	Level 3
4444	3/3/2023	Level 4
4444	3/4/2023	Level 4
4444	3/5/2023	Level 5
5555	5/5/2023	Level 1
5555	5/6/2023	Level 3
5555	5/7/2023	Level 2

a measure for each level to count the latest status of a user without double counting multile levels.

By using the a distinct count, as I do not take into consideration the date

Level 1 Count = CALCULATE(DISTINCTCOUNT(Table[CustomerID]),FILTER(Table,Table[Points]= " Level 1"))

I'm double counting Level 1 for all unique customerID

When I try to put in the date, I only get as a result 1 (as it filters all dates and keeps only the max date?)

Example that I use 

Level 1 Count = 

VAR MaxDate =

    MAX ( Table[Timestamp] )

VAR _STATUS =

    FILTER (

        Table,

        Table[Level] = Level 1

            && Table[Timestamp] = MaxDate

    )

VAR _Count =

    COUNTROWS (DISTINCT( _STATUS ))

RETURN

    _Count

It should be noted that is possible for me to get the above numbers, by adjusting the table that I'm counting and sorting by date and then removing all duplicates (customerID), so I effectively have only 1 entry per customerid(and is the latest). But in this case I cannot use a slicer for the dates

Anyones has any idea how to make this measure?

[some_bih]

Hi @aabi as @TomMartens wrote what is expected output.Below I created cal. column from your picture for MaxDatePerCustomer.  In your data same each customer at some date have only one Level ID, no two Level ID's at same date so countidistinct always return 1

Hope this help, kudos appreciated.

MaxDatePerCutomer =

VAR __max=MAX(Sheet1[Timestamp])

VAR _customer=Sheet1[CustomerID]

VAR __result=

    CALCULATE(

    MAX(Sheet1[Timestamp]),

    FILTER(ALL(Sheet1),Sheet1[CustomerID]=_customer)

    )

RETURN __result

[aabi]

Hi @some_bih 

Thank you for your input, however that that does not really help.

Let me explain a bit more what I need.. first of all the dataset I have has around 20K users. From 1 timestamp up to 10+ timestamps, and someone can start from level 1 and become level 5.

The endresults will look as follows :

Country	Level 1	Level 2	Level 3	Level 4	Level 5	Column Total
United Kingdom	1000	500	200	500	1234	3434
United States	500	300	100	1000	3254	5154
Japan	300	200	300	100	6549	7449
Germany	200	100	100	5123	9477	15000
Rows Total	2000	1100	700	6723	20514	31037

The country exist in another table (unique based on customerID, so that not an issue)

Level 1,2,3,4,5 and Total - Columns would be DAX measures
Column Total = Level 1 + 2+3+4+5
Rows Total = Level Dax Formula count

Espected results based on dataset that I have posted

Country	Level 1	Level 2	Level 3	Level 4	Level 5	Total
XXXX	1	2	1	0	1	5
Total	1	2	1	0	1	5

Where "Level 1" = 1 due to 

CustomerID	Timestamp	Level
2222	01/02/2023	Level 1
2222	01/03/2023	Level 1
2222	01/04/2023	Level 1

Level 2 = 2

CustomerID	Timestamp	Level
1111	01/01/2023	Level 1
1111	01/03/2023	Level 1
1111	10/18/2023	Level 2
5555	05/05/2023	Level 1
5555	05/06/2023	Level 3
5555	05/07/2023	Level 2

Level 3 = 1

CustomerID	Timestamp	Level
3333	05/06/2023	Level 1
3333	05/07/2023	Level 2
3333	05/08/2023	Level 3

Level 4 = 0

As there is no-one level 4 last date.

Level 5 = 1

CustomerID	Timestamp	Level
4444	03/03/2023	Level 4
4444	03/04/2023	Level 4
4444	03/05/2023	Level 5

[some_bih]

Hi @aabi I do not fully understand your logic, let me explain why not.

In your data, format of date is DD/MM/YYYY?

For Level 1 I have that Max date per level is as shown on picture below for Cal. column MaxDatePerLevel

Question for Level 4: how that for Level 4 you expect 0 when are some customer and dates?

MaxDatePerLevel for Level 2 is as below

MaxDatePerLevel =

VAR __max=MAX(Sheet1[Timestamp])

VAR __level=Sheet1[Level]

VAR __result=

    CALCULATE(

    MAX(Sheet1[Timestamp]),

    FILTER(ALL(Sheet1),Sheet1[Level]=__level)

    )

RETURN __result

[aabi]

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

[some_bih · Accepted solution]

Hi @aabi please create another column to check true false last date for customer

True False LastDateForCustomer = Sheet1[MaxDatePerCutomer]=Sheet1[Timestamp].

After that create overvies as shown on Output (slice True)

Did I answer your question? Kudos appreciated / accept solution!

Output

[some_bih]

Hi @aabi 

If this is solution, please accept it so other member of community could use it. Thank you

[aabi]

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

[TomMartens]

Hey @aabi ,

what is the expected result based on the sample data you provided?

Regards,

Tom

[aabi]

Hi @TomMartens 

Well my sample might not be the best, but the results should have been 

Level 1 Count = 1

Only UserID 2222, is level 1 on the last date.

I would also have the same formula as "level 2 count" which should be 2 (UserID 1111 and 5555)