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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors