Accumulate distinct count with Date slicer

[MinCK]

Hi everyone, 

Would love your expertise in this DAX. I am working on creating DAX to get the Total Distinct Count as in the example. The total distinct count the product category in each month (based on slicer), with a condition that the product must have more than 1 transaction. As in the example, when I select Quarter 1 on slicer, it should give me a total of 4 distinct product that have been selling since January.
This is the code I got so far to get the Distinct Count, but I can't find a way to get Total Distinct Count as I expect

CALCULATE(

    DISTINCTCOUNT(Table[StoreID]),

    FILTER(

        SUMMARIZE(

            Table,

            Table[StoreID],

            "DetailSum", COUNT(Total[ProductCat])

        ),

        [DetailSum] > 1
       )
)

Best,

[danextian · Accepted solution]

Hi @MinCK 

Ensure that you have a separate date dimension table where the date column has a one-to-many relationship with the fact table's date column.

Create these measures:

Distinct Count of Products with Txn > 1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product Category] ),
    FILTER (
        'Table',
        COUNTROWS (
            CALCULATETABLE (
                'Table',
                ALLEXCEPT ( 'Table', 'Table'[Product Category], Dates[yr mo] )
            )
        ) > 1
    )
)



Cumulative Count of Products with Txn > 1 = 
CALCULATE (
    [Distinct Count of Products with Txn > 1],
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

[MinCK]

Thank you sir this one works for me.

[Deku]

If the total ignores the current month, but still respects the date slicer, we can use allselected( dateTable ) 

CALCULATE(
    DISTINCTCOUNT(Table[StoreID]),
    CALCULATETABLE(
       FILTER(
         SUMMARIZE(
            Table,
            Table[StoreID],
            "DetailSum", COUNT(Total[ProductCat])
          ),
          [DetailSum] > 1
       ),
       ALLSELECTED( DateTable )
)

[freginier]

 Hey there!

To achieve the Total Distinct Count of products that have more than one transaction across months in DAX, you need to count distinct product categories while ensuring they appear more than once across the dataset.

Try using the following DAX formula:

TotalDistinctCount =
VAR ProductCount =
ADDCOLUMNS(
SUMMARIZE(
Table,
Table[ProductCat]
),
"TransactionCount", CALCULATE(COUNT(Table[ProductCat]))
)

RETURN
CALCULATE(
COUNTROWS(FILTER(ProductCount, [TransactionCount] > 1))
)

When you select Q1 in a slicer, the formula will count all product categories that appear more than once.

Hope this helps!

😁😁