cancel
Showing results for
Did you mean:
Helper I

## Cumulative Sum

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!

1 ACCEPTED SOLUTION
Helper II

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)))

6 REPLIES 6
Microsoft

@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] )
)
)
)```

Helper I

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.

Community Champion

@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]))))

Lima - Peru
Resident Rockstar

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 .....

Community Champion

@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] )
)
)```
Helper II

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)))

Announcements