Creating a cumulative measure that only displays up to current date

[kaylastarr]

Hello,

I am looking to create a chart similar to this one in excel

I have been able to create the Plan line without any issue but the "Actual Cumulative" line is giving me problems. My data has values for the whole year because we are forecasting out. But I only want to include in the measure values from before today. 

I am using the folling measure to get the cumulative values

Running Savings = CALCULATE(sum('Project Lists'[Savings]),All('Calendar'[Date]),'Calendar'[Date]<=max('Calendar'[Date]) && 'Calendar'[Date]<=today(),'Project Lists'[Project Stage]<>"Hopper")

This measure results in the values no longer cumulating after today, but they are still populated for the full year, resulting in a graph like below 

How can I only graph, the measure for current dates?

I have a seperate Dates table that I am using for the X Axis. 

Here is an example of my data

Project Name	Date	Savings
Pumps 111246-20 scrap reduction	1/15/2023	0
TX Offsite Storage	1/15/2023	0
EPM_2023_015	1/15/2023	5000
Reduce Shared Services spend for SAP head	1/15/2023	8000
Remote Resolution Tech Support	1/15/2023	0
Reduce Shared Services spend for SAP head	2/15/2023	8000
Remote Resolution Tech Support	2/15/2023	0
TX Offsite Storage	2/15/2023	0
Pumps 111246-20 scrap reduction	2/15/2023	0
EPM_2023_015	2/15/2023	5000
Pumps 111246-20 scrap reduction	3/15/2023	0
EPM_2023_015	3/15/2023	5000
TX Offsite Storage	3/15/2023	0
Remote Resolution Tech Support	3/15/2023	0
Reduce Shared Services spend for SAP head	3/15/2023	8000
Reduce Shared Services spend for SAP head	4/15/2023	8000
EPM_2023_015	4/15/2023	5000
TX Offsite Storage	4/15/2023	1000
Pumps 111246-20 scrap reduction	4/15/2023	0
Remote Resolution Tech Support	4/15/2023	0
Remote Resolution Tech Support	5/15/2023	0
Reduce Shared Services spend for SAP head	5/15/2023	8000
Pumps 111246-20 scrap reduction	5/15/2023	0

[kaylastarr · Accepted solution]

Just wanted to let everyone know that I was able to get it to work using this measure

Running Savings = 

Var Savings = CALCULATE(sum('Project Lists'[Savings]),All('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date]),'Project Lists'[Project Stage]<>"Hopper", USERELATIONSHIP('Calendar'[Date],'Project Lists'[Date]))
Return If(Max('Calendar'[Date])<=TODAY(),Savings,Blank())

I think I had to specify which date to use. 

[Ashish_Mathur]

Hi,

This simple measure should do the job.

Running Savings = if(EOMONTH(today(),0)<=min('calendar'[Date]),CALCULATE(sum('Project Lists'[Savings]),DATESYTD('Calendar'[Date],"31/12"),'Project Lists'[Project Stage]<>"Hopper"))

To your visual, ensure that you drag Year and Month name from the Calendar Table.

Hope this helps.

[kaylastarr]

Hello, This solution results in an inverted line. All values but the previous months are filled in, and there is no running total

[amitchandak]

@kaylastarr , Try like

Running Savings = if(max('Calendar'[Date]) <=today(), CALCULATE(sum('Project Lists'[Savings]),All('Calendar'[Date]),'Calendar'[Date]<=max('Calendar'[Date]) \ ,'Project Lists'[Project Stage]<>"Hopper"), blank())