Actuals + Forecast Total

[LaurenceSD]

Hi,

I'm trying to get a formula when I can add together the actuals so far and then the forecast in one row. This formula nearly does what I want

Enquiries&Budget = if(ISBLANK([Enquiries]),[Enquiries Budget All SD],[Enquiries]) & it gives the table on the right (table on the left is just enquiries on their own). The problem is the sum column on the Enquiries&Budget, where it's just summing up the enquiries, not enquiries & budget. Is there a way around this? And also, what i'd like it to do is instead ofreplcing Enquiries with Budget when there's not data, to only replace it for the current month and the future (i.e. using the monthoffset column in my dates table)

Thanks

[johnt75 · Accepted solution]

The problem with your total column is that when you evaluate [Enquiries] for the entire year it is not blank, and so it returns the value for [Enquiries]. You can get around that by doing a SUMX over the values of your year month column from the date table.

Enquiries & Budget =
SUMX (
    SUMMARIZE ( 'Date', 'Date'[Year month], 'Date'[On Or After Current Month] ),
    IF (
        'Date'[On Or After Current Month] = TRUE,
        [Enquiries Budget All SD],
        [Enquiries]
    )
)

The 'Date'[On Or After Current Month] column assumes a flag in your date table to show which months are either current month or in the future. This will then use the budget for those months and the enquiries for everything in the past.