Running total but stop after the last value in the fact table (solutions tried break running total)

[JakeHRogers]

Hello,

I am trying to create a cumulative total measure that stops after the last transaction in the fact table (the date in the date dimension table continues into the future):

The current measure (without trying to stop the cumulative total):

Total Price running total in DateKey = 
CALCULATE(
	SUM('Fact Transaction'[Total Price]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
                'Dim YearMonth'
                ,'Dim YearMonth'[Sort]
                ,'Dim YearMonth'[DateKey]
			),
			ALLSELECTED('Dim YearMonth')
		),
		ISONORAFTER(
			'Dim YearMonth'[Sort], MAX('Dim YearMonth'[Sort]), DESC,
			'Dim YearMonth'[DateKey], MAX('Dim YearMonth'[DateKey]), DESC
		)
    )
)

After reviewing other posts on the forum, I tried to stop the cumulative total by adding:

&& 'Dim YearMonth'[DateKey] <= MAX('Fact Transaction'[Date Key])

Like this:

Total Price running total in DateKey = 
CALCULATE(
	SUM('Fact Transaction'[Total Price]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
                'Dim YearMonth'
                ,'Dim YearMonth'[Sort]
                ,'Dim YearMonth'[DateKey]
			),
			ALLSELECTED('Dim YearMonth')
		),
		ISONORAFTER(
			'Dim YearMonth'[Sort], MAX('Dim YearMonth'[Sort]), DESC,
			'Dim YearMonth'[DateKey], MAX('Dim YearMonth'[DateKey]), DESC
		)
        && 'Dim YearMonth'[DateKey] <= MAX('Fact Transaction'[Date Key])
    )
)

That worked but it caused issues with the cumulative total - any months with no transactions now do not show the cumulative total unlike before:

I have created a mock PBIX with the data - any help on this would be much appreciated as sunk a lot of time into it now:

Running Total Issue.pbix 

Thanks

[sjoerdvn · Accepted solution]

if you want to retain the filter on item3, try this:

sjoerdvn = 
VAR dis = ALLSELECTED('Dim Item')
VAR ldf = CALCULATE(MAX('Dim YearMonth'[Sort]), ALL('Dim YearMonth'),TREATAS(CALCULATETABLE(VALUES('Fact Transaction'[Date Key]), ALL(), dis), 'Dim YearMonth'[DateKey]))
VAR lds = MAX('Dim YearMonth'[Sort])
RETURN
IF (lds <= ldf
  ,CALCULATE(
	SUM('Fact Transaction'[Total Price]),
	ALL('Dim YearMonth'),
        'Dim YearMonth'[Sort] <= lds)
)

[sjoerdvn · Accepted solution]

if you want to retain the filter on item3, try this:

sjoerdvn = 
VAR dis = ALLSELECTED('Dim Item')
VAR ldf = CALCULATE(MAX('Dim YearMonth'[Sort]), ALL('Dim YearMonth'),TREATAS(CALCULATETABLE(VALUES('Fact Transaction'[Date Key]), ALL(), dis), 'Dim YearMonth'[DateKey]))
VAR lds = MAX('Dim YearMonth'[Sort])
RETURN
IF (lds <= ldf
  ,CALCULATE(
	SUM('Fact Transaction'[Total Price]),
	ALL('Dim YearMonth'),
        'Dim YearMonth'[Sort] <= lds)
)

[JakeHRogers]

Thank you so much @sjoerdvn - the only issue is that the running total finishes for every line at the last transaction of the item that has the latest transaction date.

However, I'm accepting your reply as solution because it's a significant improvement on what I've been able to achieve so far!

[sjoerdvn · Accepted solution]

I was assuming that was what you wanted...
try this

sjoerdvn = 
VAR ldf = CALCULATE(MAX('Dim YearMonth'[Sort]), ALL('Dim YearMonth'),TREATAS(CALCULATETABLE(VALUES('Fact Transaction'[Date Key]),ALL('Dim YearMonth')), 'Dim YearMonth'[DateKey]))
VAR lds = MAX('Dim YearMonth'[Sort])
RETURN
IF (lds <= ldf
  ,CALCULATE(
	SUM('Fact Transaction'[Total Price]),
	ALL('Dim YearMonth'),
        'Dim YearMonth'[Sort] <= lds)
)

[JakeHRogers]

10/10! Thank you so much - now I can spend the afternoon understanding what you did!

[sjoerdvn]

I see.

If you want to include the months where item3 has a value, then change the first line to this:

VAR ldf = CALCULATE(MAX('Dim YearMonth'[Sort]), ALL('Dim YearMonth'),TREATAS(CALCULATETABLE(VALUES('Fact Transaction'[Date Key]), ALL()), 'Dim YearMonth'[DateKey]))

[sjoerdvn]

Also, note that that PBIX cannot be downloaded from there...

Total Price running total in DateKey = 
VAR ldf = CALCULATE(MAX('Dim YearMonth'[Sort]), ALL(), CROSSFILTER('Fact Transaction'[DateKey],'Dim YearMonth'[DateKey],Both))
VAR lds = MAX('Dim YearMonth'[Sort])
RETURN
IF (lds <= ldf
  CALCULATE(
	SUM('Fact Transaction'[Total Price]),
	ALL('Dim YearMonth'),
        'Dim YearMonth'[Sort] <= lds)
)

[JakeHRogers]

Still no joy 😞

This dropbox link should work:

https://www.dropbox.com/t/yqNdgpW40NocCAgU 

[sjoerdvn]

I see. So it is not a date dimension. Also, we can't use the datekey for determining the cumulative values. For efficiency I would recommand using a key that is sorted, because now we need an extra lookup. will post some dax below.

[sjoerdvn]

what does your date dimension table look like? I was assuming the DateKey was either a date or an integer number.

[JakeHRogers]

@sjoerdvn , the DateKey is a text field (concatenation of year, hyphen and month):

I can't use a normal date field because my user's requirements mean that I have to set the year as 1, 2, 3 etc so I can layer items from different years on top of each other on a line chart depending on whether it's their first year of existence, second year of existence etc.

If I change the reference from [DateKey] to [Sort] it works but if there's another item in the dataset with a longer date range, which I don't want to show in the chart, the values don't stop again (until the end of the date range of the item excluded on the chart)

I have a mock .pbix set up here: PBIX 

[sjoerdvn]

Just calculate the max date in the fact table first, ignoring all filters. 
Also, I think the measure can be a lot simpler

Total Price running total in DateKey = 
VAR ldf = CALCULATE(MAX('Fact Transaction'[Date Key]), ALL())
VAR lds = MAX('Dim YearMonth'[DateKey])
RETURN
IF (lds <= ldf
  CALCULATE(
	SUM('Fact Transaction'[Total Price]),
	ALL('Dim YearMonth'),
        'Dim YearMonth'[DateKey] <= lds)
)

[JakeHRogers]

Hi @sjoerdvn ,

Please see screenshot below after implementation. The cumulative total seems to stop working and values begin to appear for e.g. '1-Jan' despite this period pre-dating the start of transactions for either item:

[Alex87]

CumulativeTotal = 
VAR LastTransDate = [LastTransactionDate] (here you calculate last transaction date with Calculate ..ALL)
RETURN
CALCULATE(
    SUM('FactTable'[Amount]),
    FILTER(
        ALLSELECTED('DateTable'),
        'DateTable'[Date] <= MAX('DateTable'[Date]) &&
        'DateTable'[Date] <= LastTransDate
    )
)

[JakeHRogers]

Hi @Alex87 ,

Please see implementation in screenshot below. The cumulative total seems to stop working and values seem to appear for e.g. '1-Jan' despite this period pre-dating the start of transactions for either item: