Forum Discussion
Help with Cumulative Totals
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]))
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 youI have solved the calculation issues by adjusting the primary relationship to the date table to be active, but I now have 2 issues remaining:
1) If I make the date relationships active, the figuresare correct, but i lose connection to other sheets in the workbook because it has now made the other required relationship (Project ID) inactive. If I make the project id relationship active, the figures are incorrect.
Is there a way to make both calendar/date and the project ID field relationships active. Each of these fields exist in each of the ~10 data sheets I am working from and are required for slicer options to filter between views.
2) How do I change the cumulative and monthly calculations so the slicers work instead of calculating the total portfolio value regardless of what slicer is selected?
Thank you
