Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Comparing cumulative YTD actuals vs full year forecasts.

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 ....
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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)

toddpbi's avatar
toddpbi
Icon for Helper II rankHelper II
8 years ago

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