Stopping after last value on CUMULATIVE measure

[ronaldbalza2023]

Hi everyone, I am having a hard time figuring out how to do this. 

Question: How can I remove empty values on the cumulative measure?

Here's my cumulative measure

Total Sales - Journals (Cummulative) = 
CALCULATE( 
	[Total Sales (Journals)], 
	FILTER(
		ALLSELECTED( Dates),
		Dates[Date] <= MAX( Dates[Date])
	)
)

Total Sales (Journals) = 
CALCULATE (
    0 - SUM ( 'Journals'[Net Amount FX] ),
    FILTER (
        'Accounts',
        Accounts[Account Type] = "Sales"
            || Accounts[Account Type] = "Revenue"
    ),
    USERELATIONSHIP ( Journals[ID], 'Tracking Category CONNECTIONS'[ID] )
)

[AllisonKennedy · Accepted solution]

@ronaldbalza2023  What do you mean by 'how do you remove empty values? 

Assuming you want to stop the line before it flattens out? 

You can do this with an if statement and define the LastDate, similar to this measure I've used in the COVID NZ report https://excelwithallison.blogspot.com/search/label/COVID19 : 

Lockdown August 2021 AKL Level =

VAR _StartDate = [Lockdown August 2021 Start Date]

VAR _LastDate = CALCULATE(MAX('NZ Key Dates'[Date]), LASTDATE('NZ Key Dates'[Date]))

VAR _Duration = MAX('Days In Lockdown'[Days In Lockdown])

VAR _CaseDate = _StartDate + _Duration

VAR _KeyDate = MAXX(FILTER('NZ Key Dates', 'NZ Key Dates'[Date]<= _CaseDate), 'NZ Key Dates'[Date])

RETURN

IF( _CaseDate < _LastDate,

SUMX(

FILTER('NZ Key Dates', 'NZ Key Dates'[Date] = _KeyDate)

, 'NZ Key Dates'[AKL Level Number]

)

)

 

For your data this might look something like: 

 

Total Sales - Journals (Cummulative) =
VAR _LastDate = CALCULATE(MAX('Journals'[Date]), LASTDATE('Journals'[Date]) )
VAR _VisualDate = MAX(Dates[Date])
RETURN
IF( _VisualDate <= _LastDate,
CALCULATE(
[Total Sales (Journals)],
FILTER(
ALLSELECTED( Dates),
Dates[Date] <= MAX( Dates[Date])
)
)
)

[ronaldbalza2023]

@AllisonKennedy , thanks Allison worked like charm!