accumulated column

[Anonymous]

Hello everyone.

I have the following problem making a cumulative sum. First try to make a cumulative measure and everything works well using the following function:

cumulative measure - CALCULATE(SUM(Sheet1[Amount]), FILTER(ALLSELECTED(Sheet1), Sheet1[Date] <- MAX(Sheet1[Date])))

However, when performing a cumulative column, the sum is performed well to a certain extent, then a different value enters, to continue adding correctly, the function I use is as follows:

cumulative column - CALCULATE(SUM(Sheet1[Amount]), FILTER(ALL(Sheet1),Sheet1[Date] <- EARLIER(Sheet1[Date])))

I don't know, because it changes the sum accumulates and then recalculates correctly.

Thank you.

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description, It seems that the result of your measure is correct, while calculated column is incorrect. You can try this in the format of the calculated column to prevent it from being aggregated.

Or  you can try allselected in your calculated column.

Like this:

cumulative column =
CALCULATE (
    SUM ( Sheet1[Amount] ),
    FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[Date] < - EARLIER ( Sheet1[Date] ) )
)

If the problem isn’t solved, please feel free to ask me.

Best Regards

Janey Guo

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

[Anonymous]

Hello @v-janeyg-msft

I was reviewing your solution, but keep mis-calculating. First, I do not get the option not to summarize the data, adjunot image:

Also, using the sign function still occurs the same problem. From what you see in the data, it may be when you change the year that you do not take the data correctly.

I don't know where the problem might be. 😞

Thank you for taking the time to resolve my issue.

Best regards.

[v-janeyg-msft]

Hi, @Anonymous 

You may be a little confused about the concept of measure and column. The accumulation of column will be calculated for each date in the original table. If it is placed in the visual with years and months,the value of sum will be aggregated multiple times, resulting in a larger result. You can try with the original table's date .

Best Regards

Janey Guo

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

[amitchandak]

@Anonymous , The first formula should work. But I do see dates in ascending order in the visual so can not comment.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak Apparently when you change the year, the function stops working properly, is there any solution to that problem? Any variant of the other functions sent, so that it calculates in the same way as the function used for the measurement?

Best regards.

[Anonymous]

@amitchandak What I need to know is because the function to calculate column-aquerized sum is calculating with defects.

I have also used the following function:

SUMX (

FILTER (

Hoja1,

Sheet1[Date] <- EARLIER ( Sheet1[Date] )

),

Sheet1[Amount]

)

But it also shows errors, as seen in the image I incorporated. I don't know how to load the sample data I have. Helpyy!