SUMX for cumulative sum not working as expected

[RYU209]

Hello,

I'm trying to use SUMX to create a cumulative sum of a measure that counts the amount of distinct Projects. 

Currently I have a distinct incomplete Project count measure:

Incomplete Projects = 

CALCULATE(DISTINCTCOUNT(Table1[ProjectID]), Table1[ProjectStatus] IN {"PLN", "CUR", "ONH"})

I'm trying to view the cumulative sum of this measure over time using the following:

Cumulative Active Projects = SUMX('DateTable', [Incomplete Projects])

Here is my output:
For some reason my Cumulative Active Projects measure is not adding up over time. What am I doing wrong here?

[audreygerred · Accepted solution]

Hi! You can use a running total through quick meausures, or use the below DAX:

DistinctCount running total in Date =

CALCULATE(

    [Active Projects],

    FILTER(

        ALLSELECTED('Date'[Date]),

        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

    )

)

[RYU209]

Thank you @audreygerred, that worked perfectly. I'm still curious, though, why is my old measure not working?  

[audreygerred]

No problem! SUMX is an iterator function so it completes the summing at each row, then sums those up for a total, but it is not meant for cumulative or running total - just a row and total. Here is a blog I recently wrote abot iterators: http://powerbiwithme.com/2023/11/01/the-iterator-edition/ 

[RYU209]

OK, when I looked up similar questions to mine I saw many people using SUMX. Thank you for the explanation! 

[audreygerred]

Anytime! Thanks for asking!