topic Re: Calculate Distinct Values per Month/ Year (Running total?) in DAX Commands and Tips

Calculate Distinct Values per Month/ Year (Running total?)

D_Brouwers — Thu, 07 Mar 2024 11:59:19 GMT

Hi everyone,
This is my first post, as I've used this form many times to help me out, but I cannot find a fitting solution this time.
I have a visual in which I'd like to display the unique number of products that are checked per month and the running total (of unique products that are checked) for that year.
The number of unique products is calculated with a simple measure:

Aantal Unieke artikelen (YtD) =

CALCULATE (

DISTINCTCOUNT ( 'Voorraad vs Scanning'[Artikel] ),

FILTER (

'Kalender',

'Kalender'[Jaar] = 2023

)

)

What I can't get working is the running total. I've tried

SUMX(VALUES( 'Voorraad vs Scanning'[Artikel] ), 'Voorraad vs Scanning'[Artikel])

but that ends up with the error that SUMX cannot work with values of type string. I cannot change the type as product ref's are alphanumerical.
Any help is appreciated and the pbix can be found here (version2)

Re: Calculate Distinct Values per Month/ Year (Running total?)

some_bih — Mon, 04 Mar 2024 11:35:01 GMT

Hi D_Brouwers

usually, SUMX summing some numberical column, like quantity, price... check link for official documentation

your model is not available, so maybe you need COUNTAX

Re: Calculate Distinct Values per Month/ Year (Running total?)

D_Brouwers — Thu, 07 Mar 2024 07:18:29 GMT

Hi some_bih,

I've tried to attach the pbix for reference (I hope it worked). I've also had a look at COUNTAX, but couldn't get that to provide the desired outcome. So....if anyone has any additional suggestions, I'd love to hear them.

Re: Calculate Distinct Values per Month/ Year (Running total?)

some_bih — Thu, 07 Mar 2024 07:52:52 GMT

Hi D_Brouwers your file is not visible.

Re: Calculate Distinct Values per Month/ Year (Running total?)

D_Brouwers — Thu, 07 Mar 2024 12:00:03 GMT

Hello All,

Link has been updated. This time it should work.

Re: Calculate Distinct Values per Month/ Year (Running total?)

D_Brouwers — Thu, 14 Mar 2024 07:46:19 GMT

In the meantime I've found a solution offline that seems to work and which I'm happy to share.
As a first step I've added a calculated column to my table;

FirstOccurrence =

VAR CurrentYear = YEAR('Voorraad vs Scanning'[Datum])

VAR CurrentValue = 'Voorraad vs Scanning'[Artikel]

RETURN

IF(

MINX(

FILTER(

'Voorraad vs Scanning',

'Voorraad vs Scanning'[Artikel] = CurrentValue &&

YEAR('Voorraad vs Scanning'[Datum]) = CurrentYear

'Voorraad vs Scanning'[Datum]

) = 'Voorraad vs Scanning'[Datum],

CurrentValue,

BLANK()

)

As a final step I've counted the number of non-blank occurence of this "FirstOccurrence".