Help with Cumulative Totals

[kristi_in_heels]

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)

[isjoycewang]

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

[kristi_in_heels]

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

[kristi_in_heels]

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

[kristi_in_heels]

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

[isjoycewang · Accepted solution]

Hi @kristi_in_heels,

Exercise File

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

[kristi_in_heels]

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?