Count Unique category based on Value and Date

[ShrutiJ]

Hi,

I have data in the below format. I need a measure which will count distinct number of "Categories" which has atleast 2 "Product IDs" purchased in last 3 months.

In the below example, I have 2 product IDs purchased in Nov for Category A and 3 product IDs purchased in Oct for Category C. Records marked in Blue.

Current month is Nov, both Oct and Nov fall in last 3 months. Category C has no product purchased in last 3 months.

So I need a meaure which will retun the unique Category count, that is, 2.

Could someone please help with this?

To add, I'm using tabular model. Hence, can't create a new column. I can only create a DAX measure.

[v-zhangti · Accepted solution]

Hi, @ShrutiJ 

You can try the following methods.
Measure:

Count Product =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product ID] ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Purchase Date] )
            >= MONTH ( TODAY () ) - 3
            && [Category] = SELECTEDVALUE ( 'Table'[Category] )
    )
)

Count Unique category = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Category] ),
    FILTER ( ALL ( 'Table' ), [Count Product] >= 2 )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[ShrutiJ]

Thank you @v-zhangti ! This is working perfectly!

[Shaurya]

Hi @ShrutiJ,

Use this formula to create a flag first, that marks the Categories with atleast two Product IDs in the last 3 months:

Flag = IF(COUNTX(FILTER('Table',DATEDIFF('Table'[Purchase Date],TODAY(),MONTH)<=3),IF('Table'[Category]=EARLIER('Table'[Category]),'Table'[Product ID]))>=2,1,0)

Then create this measure to count the distinct categories based on that flag and add it in a card:

Count = CALCULATE(DISTINCTCOUNT('Table'[Category]),FILTER('Table','Table'[Flag]=1))

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[ShrutiJ]

Thanks @Shaurya for your response.

Since I'm using the tabular model, I can only create measures. I can't create the "Flag" as it should be created as a new "column".