Forum Discussion
Help with Cumulative Totals
Hello,
I have a set of data which I am trying to show in a line graph visual. I want to be able to show 2 separate lines:
- Value per month
- Cumulative Value which obviously increases per month
Columns I need to use in this visual are:
* Project 'B00 Tabulated' [Project Definition]
* Date (by Month) 'B00 Tabulated' [Date]
* Value 'B00 Tabulated' [Baseline Forecast]
I have a date table relationship linked to the date column ('Calendar' [Date]), and the project column also links to relationships with other project columns in different sheets so the slicer works fine, I just can't get the totals to show correctly.
The data is tabulated so each project has 12 lines, one per month, to show the monthly value per project.
Can anybody suggest the correct dax measures to achieve this? (Redacted snip of the data model below)
Hi kristi_in_heels,
Since you're using inactive relationship between 'Calendar' and 'Table', please modify the measures as below:
Forecast Value = CALCULATE( SUM('Table'[Baseline Forecast]), USERELATIONSHIP('Calendar'[Date], 'Table'[Date]))Cumulative Value = var maxdate= MAX('Calendar'[Date]) return CALCULATE( SUM('Table'[Baseline Forecast]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= maxdate), USERELATIONSHIP('Calendar'[Date], 'Table'[Date]))
6 Replies
Please try if this works for you. If not, please share dummy data and let us know more info about your chart.
Thanks.
Cumulative Value = var maxdate= MAX('Table'[Date]) return CALCULATE( SUM('Table'[Value]), FILTER( ALL('Table'), 'Table'[Date] <= maxdate))Thank you for your response.
The calculation works to generate the total cumulative but the line remains flat at the total 12 month value across every month (purple straight line in the image below) as opposed to an up-curve over the entire period, reaching that point in the last month.
Sample data is below for reference:
Period Project Definition Project Month Baseline Forecast Budget1 1 Name 1 July 1000 Budget1 2 Name 2 July 1000 Budget1 3 Name 3 July 1000 Budget1 4 Name 4 July 1000 Budget1 5 Name 5 July 1000 Budget1 1 Name 1 August 1500 Budget1 2 Name 2 August 1500 Budget1 3 Name 3 August 1500 Budget1 4 Name 4 August 1500 Budget1 5 Name 5 August 1500 Budget1 1 Name 1 September 2000 Budget1 2 Name 2 September 2000 Budget1 3 Name 3 September 2000 Budget1 4 Name 4 September 2000 Budget1 5 Name 5 September 2000 Budget1 1 Name 1 October 2500 Budget1 2 Name 2 October 2500 Budget1 3 Name 3 October 2500 Budget1 4 Name 4 October 2500 Budget1 5 Name 5 October 2500 Budget1 1 Name 1 November 3000 Budget1 2 Name 2 November 3000 Budget1 3 Name 3 November 3000 Budget1 4 Name 4 November 3000 Budget1 5 Name 5 November 3000 Budget1 1 Name 1 December 3500 Budget1 2 Name 2 December 3500 Budget1 3 Name 3 December 3500 Budget1 4 Name 4 December 3500 Budget1 5 Name 5 December 3500 Budget1 1 Name 1 January 4000 Budget1 2 Name 2 January 4000 Budget1 3 Name 3 January 4000 Budget1 4 Name 4 January 4000 Budget1 5 Name 5 January 4000 Budget1 1 Name 1 February 4500 Budget1 2 Name 2 February 4500 Budget1 3 Name 3 February 4500 Budget1 4 Name 4 February 4500 Budget1 5 Name 5 February 4500 Budget1 1 Name 1 March 5000 Budget1 2 Name 2 March 5000 Budget1 3 Name 3 March 5000 Budget1 4 Name 4 March 5000 Budget1 5 Name 5 March 5000 Budget1 1 Name 1 April 5500 Budget1 2 Name 2 April 5500 Budget1 3 Name 3 April 5500 Budget1 4 Name 4 April 5500 Budget1 5 Name 5 April 5500 Budget1 1 Name 1 May 6000 Budget1 2 Name 2 May 6000 Budget1 3 Name 3 May 6000 Budget1 4 Name 4 May 6000 Budget1 5 Name 5 May 6000 Budget1 1 Name 1 June 6500 Budget1 2 Name 2 June 6500 Budget1 3 Name 3 June 6500 Budget1 4 Name 4 June 6500 Budget1 5 Name 5 June 6500 I also have some calculated columns in my model to insert a column for a month and year, then generate the date for each of these as the first of the relevant month (eg 1/7/2023 or 1/6/2024)
Based on this sample data, I would like to see these totals, the same as can be generated from excel:
Month Cumulative July 5000 5000 August 7500 12500 September 10000 22500 October 12500 35000 November 15000 50000 December 17500 67500 January 20000 87500 February 22500 110000 March 25000 135000 April 27500 162500 May 30000 192500 June 32500 225000 Thank you for your assistance
I managed to get the cumulative to work using this formula:
Cumulative = CALCULATE( sum('Table'[Baseline Forecast]), filter(all('Table'), 'Table'[Date] <=max('Calendar'[Date])))But this is not changing when I adjust slicers, the total is remaining as the total for all the data. I need this to change based on slicer selection. I have checked relationships and all are correct.My monthly individual totals are also still not calculating correctly.Any ideas on how to correctly sum by individual month? I am sure this is something simple that I am missing!Thank you
