DistinctIDs for each month ids should not be repetitive.

[AgniAnkit]

Hi  everyone,

I need help in building a logic to get the distinct ids from a table for each month and ids should not be repeatitive from past month. attahced screenshot with example data. 

help would be much appriciated.

[tamerj1 · Accepted solution]

Hi @AgniAnkit 

Please try

DistinctCount =
COUNTROWS (
EXCEPT (
VALUES ( Data[ID] ),
CALCULATETABLE (
VALUES ( Data[ID] ),
'Date'[Start of Month] < MAX ( 'Date'[Start of Month] ),
ALL ( 'Date' )
)
)
)

[AgniAnkit · Accepted solution]

Thankyou so much. It is giving the correct counts, however total is not coming correct , it is showing last month count.

[AgniAnkit]

Thankyou so much 🙂

[tamerj1 · Accepted solution]

Hi @AgniAnkit 

Please try

DistinctCount =
COUNTROWS (
EXCEPT (
VALUES ( Data[ID] ),
CALCULATETABLE (
VALUES ( Data[ID] ),
'Date'[Start of Month] < MAX ( 'Date'[Start of Month] ),
ALL ( 'Date' )
)
)
)

[AgniAnkit · Accepted solution]

Thankyou so much. It is giving the correct counts, however total is not coming correct , it is showing last month count.

[tamerj1]

@AgniAnkit 

What should be the total? Do you want to sum the values of each month over the months? Should be doable but what meaning does that total hold?

[AgniAnkit]

Sum of the values of each month.

Thankyou

[tamerj1 · Accepted solution]

@AgniAnkit 

Please try

DistinctCount =
SUMX (
VALUES ( 'Date'[Month] ),
CALCULATE (
COUNTROWS (
EXCEPT (
VALUES ( Data[ID] ),
CALCULATETABLE (
VALUES ( Data[ID] ),
'Date'[Start of Month] < MAX ( 'Date'[Start of Month] ),
ALL ( 'Date' )
)
)
)
)
)