Cumulative Sum

[spencer_g]

I posted yesterday, and have since resolved part of the original problem, but I am still trying to figure out how to create a cumulative sum for 3 years of data. 

I am trying to create a line chart that shows the number of employers registered for a program over a 3 year period with the chart broken down into months. I am currently using the following formula:

Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'[Date Created]), 'I Employers'[Date Created] <= "2020-12")))

This forumla yields the following chart, which only provides the number of employers who initially registered each month, not a running/cumulative total over the 3 year period.

I've also tried using the following forumla, which has the same outcome:

Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'[Date Created]), 'I Employers'[Date Created] <= "MAX('I Employers'[Date Created])))

Any suggestions? Thanks!

[Eric_Zhang]

---

@spencer_g wrote:

I posted yesterday, and have since resolved part of the original problem, but I am still trying to figure out how to create a cumulative sum for 3 years of data. 

I am trying to create a line chart that shows the number of employers registered for a program over a 3 year period with the chart broken down into months. I am currently using the following formula:

Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'[Date Created]), 'I Employers'[Date Created] <= "2020-12")))

This forumla yields the following chart, which only provides the number of employers who initially registered each month, not a running/cumulative total over the 3 year period.

I've also tried using the following forumla, which has the same outcome:

Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'[Date Created]), 'I Employers'[Date Created] <= "MAX('I Employers'[Date Created])))

Any suggestions? Thanks!

---

@spencer_g

What is the type of [Date Created]? I see it is compared with a text "2020-12" in the snapshot, so I think it is text type? If it is a text, how can you get the last expression parsed while [Date Created] is wrapped by MAX. I got confused.

In the case that [Date Created] is text, change it to date. Drag a line chart, new a [Date Create] hierarchy and use it as the Axis. Change the Cumulative Employers as

Cumulative Employers = 
CALCULATE (
    SUM ( 'I Employers'[Employer Count] ),
    (
        FILTER (
            ALL ( 'I Employers'[Date Created] ),
            'I Employers'[Date Created] <= MAX ( 'I Employers'[Date Created] )
        )
    )
)

[spencer_g]

Thanks for your response. I figured it was that [Date Created] was text, so I used the original data which was saved as a date, [Org Date Created], and put it into the formula you recommended:

Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'[Org Date Created]), 'I Employers'[Org Date Created] <= MAX('I Employers'[Org Date Created]))))

However, I still come up with the same result. The line graph is cumulated by year, but not each year buildling upon the year before it. 

[Vvelarde]

@spencer_g

Try this

Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'), 'I Employers'[Org Date Created] <= MAX('I Employers'[Org Date Created]))))

[Baskar]

Hi Spence_g

I suggest two ways for you.

1. Use the original measure without creating calculate measure, then u could apply the visual level filter as

"'I Employers'[Date Created]" and choose  year should greater or less then which u want.

2. Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'[Date Created]),

Year('I Employers'[Date Created]) >= 2001 )))  -- Here change the year whatever you want.

it will work , check it if not let me know i will be assist u .....

[Sean]

@spencer_g This should work! Let me know...

Cumulative Employers =
CALCULATE (
    SUM ( 'I Employers'[Employer Count] ),
    FILTER (
        ALL ( 'I Employers' ),
        'I Employers'[Date Created] <= MAX ( 'I Employers'[Date Created] )
    )
)

[MR2001 · Accepted solution]

I had a similar issue, and would suggest a small modification:

1. Add a slicer on field (choose a filed in your table)

2. Cumulative Employers = CALCULATE(sum('I Employers'[Employer Count]),(FILTER(ALL('I Employers'[Date Created]), 'I Employers'[Date Created] <= "2020-12"), VALUES(field)))