Distinct count by max date

[lnik]

Hi everyone,

I am trying to calculate distinct values based on max date column.

But with measure 

DistinctCount = calculate(DISTINCTCOUNT(Table1[Item]),Table1[Date Visit]=MAX(Table1[Date Visit]))

it count also Item 3 although he is not with max date.

Thank you!

[Anonymous · Accepted solution]

Hi,@lnik 

I am glad to help you. 

According to your description, you want to distinct count by max date? 

If I understand you correctly, then you can refer to my solution. 

1. You can start by creating a new table. 

Table 2 = 
VAR _distinct =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        'Table'[Customer],
        'Table'[Date Visit],
        "MaxDate",
            CALCULATE (
                MAX ( 'Table'[Date Visit] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Item] = EARLIER ( 'Table'[Item] ) )
            )
    )
RETURN
    FILTER (
        _distinct,
        IF ( 'Table'[Item] = 3, BLANK (), 'Table'[Date Visit] = [MaxDate] )
    )

2. Create a Measure for calculating the number of rows corresponding to each Item, and you'll end up with the result you want. 

DistinctCount = 
VAR _currentItem =
    MAX ( 'Table 2'[Item] )
RETURN
    COUNTROWS ( FILTER ( ALL ( 'Table 2'[Item] ), 'Table 2'[Item] = _currentItem ) )

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lnik]

Thank you, works perfectly!

One more question.

Is it possible if i add slicer for year and month, when selecting a specific month it dynamically to calculate distinct count for the items based on the maximum date for that month?

[Rakesh1705]

Hello friend, 
I have tried the same formula you mentioned...its working....sharing the snaps

Please let me know the problem you are facing.

[lnik]

I want to use ut in matrix visualisation but there it shows 1 against item 3 and I expect it to be empty