cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karimk
Helper III
Helper III

Monthly Cumulative Result Not Working

Hi everyone.

I´m having a problem getting the correct result with a measure for cumulative monthly results.

My goal: get monthly cumulative values on a line chart for my "rent / square meter" cost. 

Database: One row for each building, for each month. My main columns are "Rent", "Square Meters", "Month" and "Benchmark".

Measures Created So Far:

  • Rent Sum
  • Square Meters Sum
  • Square Meters Cost --> DIVIDE (Rent Sum; Square Meters Sum)

I have the dataset on the following Dropbox link.

https://www.dropbox.com/s/c46paa0lc3xyftv/Dataset%20Example.xlsx?dl=0

 

It seems that when I try the usual CALCULATE(datecolumn;FILTER(ALL(datecolumn;datecolumn <= MAX (datecolumn))) it will give me the same result as the monthly non cumulative value.

The correct values should be the ones displayed in the "Table" sheet of the database above.

 

Any help?

 

Thanks

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my solution from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my solution from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you very much!

If I have a chart that spans 2 or more years, the DATESYTD formula will only calculate the cumulative value within each year? If yes, what should I do when I want the monthly cumulative value starting on the first and ending on the last years selected?

You are welcome.  I have updated the file with a new measure which cales yield since inception.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.