topic Running Total for product numbers by categories in DAX Commands and Tips

Running Total for product numbers by categories

00Kevin00 — Wed, 26 Jun 2024 14:19:03 GMT

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.

Re: Running Total for product numbers by categories

Jihwan_Kim — Wed, 26 Jun 2024 16:11:55 GMT

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] ) )

Re: Running Total for product numbers by categories

00Kevin00 — Mon, 01 Jul 2024 07:37:19 GMT

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