Running Total for product numbers by categories

[00Kevin00]

Hello experts,

I hope someone can help me with my DAX formula.

I have the following situation: I wish to create a Measure which gives me the running total for a distinct count of my product numbers by their publishing year. For this, I created the following measure:

Running Total product number (Publishing Year) =
CALCULATE (
    DISTINCTCOUNT('Product'[Product number]),
    FILTER (
        ALL ( 'Product'[Publishing Year] ),
        'Product'[Publishing Year] <= MAX ( 'Product'[Publishing Year] )
    )
)

This measure works just fine, when no further level of detail is required. However, in combination with the use of categories (in my case a product brand clustering). The running total only extends to the last year in which a product is published. This leads to incorrect totals:

E.g. The red part in 2028 would be 171 and not 0.

Currently, my DAX formula leads to this result in a table format:

 

What I would like to achieve is the following:

Note: Publishing year is not a in separate date table but an integer value in the product table.

[Jihwan_Kim]

Hi,

Please try something like below whether it suits your requirement.

Running Total product number (Publishing Year) =
COUNTROWS (
    SUMMARIZE (
        FILTER (
            ALL ( 'Product' ),
            'Product'[Product number] = MAX ( 'Product'[Product number] )
                && 'Product'[Publishing Year] <= MAX ( 'Product'[Publishing Year] )
        ),
        'Product'[Product number]
    )
)

[00Kevin00]

Unfortunately, this does not lead to the wished result. Below you can see the outcome with this DAX-formula: