Forum Discussion

PBIuser_2024's avatar
PBIuser_2024
New Member
2 years ago
Solved

Distinct count

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. 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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.

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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.