Distinct count

[PBIuser_2024]

Hi Everyone,

I am trying to find the distinct count of products for the months of Jan - May 24, but if the product appeared in Jan this should not be counted in Feb, Mar, Apr, May. 

After the first occurance it should not be counted in the time series. Is this possible in Power BI?

Thanks v much in advance. 

[PBIuser_2024]

thanks @rajendraongole1 

I get a "Visual has exceeded the available resources" error message using the measure.

[rajendraongole1 · Accepted solution]

Hi @PBIuser_2024  you can create a measure with first month occurrance as below:

FirstMonthOccurrence =
CALCULATE(
MIN('DateTable'[MonthYear]),
ALLEXCEPT('Sales', 'Sales'[Product])
)

to get the distinct product by month which is not repeated in existing month, create one more new measure

DistinctProductsByMonth =
VAR SelectedMonthYear = MAX('DateTable'[MonthYear])
RETURN
CALCULATE(
DISTINCTCOUNT('Sales'[Product]),
FILTER(
'Sales',
[FirstMonthOccurrence] = SelectedMonthYear
)
)

drag the above measure and check each product is counted only in its first occurrence month and not in subsequent months.

Regards,

Rajendra

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.