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
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]))