Cumulative Sum

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

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

---

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