Cumulative Sum of a column with blanks and negatives

[Novice100]

Hello Community,

I need help in writing a DAX to calculate cumulative sum of a cloumn that includes blanks and negatives. I tried with solutions provided in the community but I get the total value in each and every row. Below is the show data and the result which I desire, where the first column is of format Date/Time: 

Thank you in advance,

Saurav

[Anonymous · Accepted solution]

Hi @Novice100 ,

Please refer to my pbix file to see if it helps you.

Insert an index column.

Then create  a measure.

Measure =
CALCULATE (
    SUM ( 'Table'[C] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
            && 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] )
    )
)

Or a column.

column =
CALCULATE (
    SUM ( 'Table'[C] ),
    FILTER (
        'Table',
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
            && 'Table'[ID] = EARLIER ( 'Table'[ID] )
    )
)

If I have misunderstood your meaning, please provide your desired output with more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Novice100]

@Anonymous Hi, 

Thank you! It works perfectly fine.

I tried before by adding new index column but had issues with my data but after the refresh it works. 

Regards

@Novice100

[Jihwan_Kim]

Hi,

I am not sure whether I missed something from your question, but may I ask how you define the sequence between A2 and A3, for instance, which one comes first? 

I think, if you can bring in the "Second" information into the column, it should work. If you cannot, then I suggest having a index column additionally that shows the order of each occurence.

Thank you.

[Novice100]

Hello, 

Thanks! for the reply.

There is a slight increment in time in seconds and miliseconds between A2 and A3.

For ex: A2 = 10/17/2021 12:36:45

A3 = 10/17/2021 12:36:46. and so on

I also tried with index column but had same issue of repeating total in each rows.

[Jihwan_Kim]

Hi,

I think you have "Second" information to differenciate the order. 

However, I am not sure your power bi data model brought the differeciating factor into the model.

Share your sample pbix file, and then I can try to look into it to come up with more accurate solution.

Thanks.

[Novice100]

Hello Sir, I am sorry but due to confidentiality issue I cannot share my pbix file. But I have an extra ID column however, I am not sure if it can help to differentiate the order. Something as below: