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]))
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:
I 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]))Thank you, this works perfectly.
EDIT - Below query solved. One of my calculated columns missed the correct year application so was throwing the dates out.
******
One question: when I hover over a certain month, one of the monthly figures doesn't show in the tooltip box. This is the system generated tooltip, not one I have created. The cumulative totals seem to still be correct, just some of the monthly figures drop out of the list of values. Would there be any reason for this?