cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
spencer_g
Helper I
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.

 

 

Chart 1.jpg

 

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
MR2001
Helper II
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))) 

 

View solution in original post

6 REPLIES 6
Eric_Zhang
Microsoft
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.

 

 

Chart 1.jpg

 

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

Capture.PNG

 

 

 

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. 

 

chart.jpgtable.jpg

Vvelarde
Community Champion
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
Baskar
Resident Rockstar
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 .....

 

 

 

Sean
Community Champion
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] )
    )
)
MR2001
Helper II
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))) 

 

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!