Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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)))
@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!
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] ) ) ) )
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.
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]))))
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 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] ) ) )
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)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |