Cumulative Sum problem

[OscarSuarez10]

Hello I want to calculate the cumulative sum of UniquePageViews, to get this results:

Date	UniquePageViews	Running Total Sume
1	5	5
2	25	30
3	70	100
4	8	108
1	32	32
2	1	33
3	25	58
4	36	94
1	25	68
2	43	65
3	22	87
4	10	97

As you can see column dates has a repeated sequence of dates.

Im using the following formula to cerate a colum to do this but Im getting a wrong cumulative sum in the column Running Total Sume as you can see there:

I dont know why it doesnit work for repeated dates, 

Can you help me?

[sturlaws · Accepted solution]

Hi @OscarSuarez10 

Let us take a look at what happens when you look at Hoja[Date]=1, with special attention to this part:

Hoja[Date] <= Earlier(Hoja[Date])

When you look at a row where [Date]=1, this code says find all rows where [Date] is smaller or equal to 1. Hence, from you example, it sums 5, 32 and 25 = 62.

So how to fix this? You are relying on the sorted order of your table. Since you are looking at UniquePageViews, perhaps you have a uniquePageID available? If you have, you could change your code to this:

Running Total Sume =
CALCULATE (
    SUM ( Hoja3[UniquePageViews] );
    FILTER (
        ALLSELECTED ( Hoja3 );
        Hoja3[date] <= EARLIER ( Hoja3[Date] )
            && Hoja3[UniquePageID] = EARLIER ( [UniquePageID] )
    )
)

I have rewritten you code using the ALLSELECTED-function, although ALLSELECTED is a particular difficult function to master. I can't see it has any uses for you here, and unless you have found that it has a purpose in your code, I would recomend that you change it to use the ALL-function.

Now, if don't have a something like a uniquePageID available, we need to rely on the order of Date in the input, and generate a uniqe ID on our own. There are a few steps to this, so instead of describing them, I have created a demo report

Go to Power Query/Edit queries to see how this is done.

Cheers,
sturla

If this post helps, then please consider Accepting it as the solution. Kudos is also nice.

[OscarSuarez10]

Thank You @sturlaws  It solved the problem!