Comparing cumulative YTD actuals vs full year forecasts.

[Anonymous]

I am comparing Cumulative actuals against forecasts (see screenshot) . I want to only show cumulative actuals up until the most recent month for which we have actuals. This is month 8 / August 2017 . So I don't want tp show cumulative actuals for months 9-12.

I would have thought this could be achieved  by adding month filter with the CALCULATE function in the "Cumulative Actuals" measure :

Cumulative Actuals =
CALCULATE (
SUM ( 'Dataset'[Amount]), 'Dataset'[Type]="Actuals", 
FILTER (
ALL ('DateDim'[Date]),
'DateDim'[Date] <= MAX ( 'DateDim'[Date])
)
)

However, I am getting errors.  Can anyone point me in the right direction?. PBIX file here. 

[toddpbi]

Hi there stfox1,

If you are using a monthly table without using Time Intelligence through a Date Type column in your connected date table, you can try the following DAX formula:

Cumulative Actuals = IF( LASTDATE( 'YOUR DATE TABLE'[Date Column]) > TODAY(),
    BLANK(),
        CALCULATE( [YOUR TOTAL ACTUAL MEASURE],
            FILTER( ALLSELECTED( 'YOUR DATE TABLE' ),
                'YOUR DATE TABLE'[Date Column] <= MAX( 'YOUR DATE TABLE'[Date Column )))

This formula will return the actuals for which there is data up until "today". Any date further than this will blank out the values exceeding today. This is what I used for monthly aggregated data with a DateKey connected to a date table, without the use of in-built time intelligence functions.

Hope this works for you 🙂

[Phil_Seamark · Accepted solution]

Hi @Anonymous

I think this calculated measure is pretty close.  Just use this code in place of yours.  I'd suggest you use date columns that are datetime rather than just a Month number.

Cumulative Actuals = 
VAR Good = 
	CALCULATE (
		SUM ( 'Dataset'[Amount]), 'Dataset'[Type]="Actuals",
		FILTER (
			ALL ('DateDim'[Date]),
			'DateDim'[Date] <= MAX ( 'DateDim'[Date])
		)
	)
VAR MaxDate = MAXX(FILTER('Dataset',NOT ISBLANK('Dataset'[Actuals_month])),RELATED(DateDim[Date]))
	
RETURN 
	IF(MAX('DateDim'[Date]) <= MaxDate , Good)	

[Anonymous]

Works perfectly. One question in relation to this measure. Usually our  most recent month of actuals  (e.g. August 2017 in the screenshot ) is a partial month (e.g we might only be 10 days into the most recent month). We are only interested in showing the cumulative total for complete months. So the  most recent month minus one  month (so up to July 2017). 

  I assume this involves a DATEADD(-1 month etc) , but I haven't managed to sucessfully tweak your code to include it.

Is there a way to adapt this DAX so that we exclude the last month from the cumulative total for actuals? 

[v-xjiin-msft]

Hi @Anonymous,

I have made some modification based on @Phil_Seamark's solution. Please make a check, see if it helps:

Cumulative Actuals =
VAR MaxDate =
    MAXX (
        FILTER ( 'Dataset', NOT ISBLANK ( 'Dataset'[Actuals_month] ) ),
        RELATED ( DateDim[Date] )
    )
VAR Good =
    IF (
        MONTH ( MaxDate ) = MAXX ( ALL ( 'Dataset' ), 'Dataset'[Actuals_month] ),
        BLANK (),
        CALCULATE (
            SUM ( 'Dataset'[Amount] ),
            'Dataset'[Type] = "Actuals",
            FILTER ( ALL ( 'DateDim'[Date] ), 'DateDim'[Date] <= MAX ( 'DateDim'[Date] ) )
        )
    )
RETURN
    IF ( MAX ( 'DateDim'[Date] ) <= MaxDate, Good )

Thanks,
Xi Jin.

[Anonymous]

Excellent - Works perfectly