Calculate full year forecast as Actuals + Forecast

[paliman]

Hello everyone

I need some help with an Actuals + Forecast issue

We have forecasts updated on a quarterly basis, so that in March we plan for the period April to December. I need to calculate a full year forecast adding the Actuals for the previous months. Basically, I need the FY Forecast column:

I have tried
IF(ISBLANK([Forecast]),[Actual],[Forecast])
but as time goes and I have actual data, I found the following problem:

[paliman]

Something went wrong and this picture was not posted, What I need is the FY Fcst Col. below:

[v-shex-msft]

Hi @paliman,

Since I not so clear for your table data structure, can you please share some dummy data to test?

How to Get Your Question Answered Quickly 

In addition, you can take a look at the following blog to know how to handle blank values and use it with if statements:

Handling BLANK in DAX 
Regards,

Xiaoxin Sheng

[paliman]

Ok, sorry for the mess, I will try to explain it better.

I have two tables : Actuals and Forecasts; Forecast is updated on a quarterly basis.

I want to calculate the Full Year Forecast as follows:

For Q2 Forecast, which contains data for the period April to December, FY Forecast = Actuals Jan-Mar + Fcst Apr-Dec

I've tried the very simple approach of IF(ISBLANK([Forecast]),[Actuals],[Forecast]), but my problem is that if a particular product or customer was not forecasted, the result will be the actual value instead of a zero or a blank. Please see screenshot below. I have also posted a link to my Google Drive with the model I used for this example.

Thank you very much in advance

https://drive.google.com/file/d/1nasnWLt8eqmIgko9TimuOeLDPIcnUNDM/view?usp=sharing 

[v-shex-msft · Accepted solution]

HI @paliman,

You can use the following measure formulas to get the replaced total based on customer or product group:

FY Customer = 
VAR merge =
    ADDCOLUMNS (
        UNION (
            ADDCOLUMNS ( Actuals, "Source", "Actuals" ),
            ADDCOLUMNS ( Foreast, "Source", "Foreast" )
        ),
        "Month", LOOKUPVALUE ( 'Calendar'[Month], 'Calendar'[Date], [Date] )
    )
VAR summary =
    SUMMARIZE (
        merge,
        [Month],
        [Customer],
        "FY",
        VAR _fs =
            SUMX (
                FILTER (
                    merge,
                    [Month] = EARLIER ( [Month] )
                        && [Customer] = EARLIER ( [Customer] )
                        && [Source] = "Foreast"
                ),
                [Sales]
            )
        VAR _as =
            SUMX (
                FILTER (
                    merge,
                    [Month] = EARLIER ( [Month] )
                        && [Customer] = EARLIER ( [Customer] )
                        && [Source] = "Actuals"
                ),
                [Sales]
            )
        RETURN
            IF ( _fs = BLANK (), _as, _fs )
    )
RETURN
    IF (
        ISINSCOPE ( Customers[Customer Name] ),
        IF ( ISBLANK ( [Forecast Sales] ), [Actual Sales], [Forecast Sales] ),
        SUMX ( summary, [FY] )
    )

FY Product = 
VAR merge =
    ADDCOLUMNS (
        UNION (
            ADDCOLUMNS ( Actuals, "Source", "Actuals" ),
            ADDCOLUMNS ( Foreast, "Source", "Foreast" )
        ),
        "Month", LOOKUPVALUE ( 'Calendar'[Month], 'Calendar'[Date], [Date] )
    )
VAR summary =
    SUMMARIZE (
        merge,
        [Month],
        [Product],
        "FY",
        VAR _fs =
            SUMX (
                FILTER (
                    merge,
                    [Month] = EARLIER ( [Month] )
                        && [Product] = EARLIER ( [Product] )
                        && [Source] = "Foreast"
                ),
                [Sales]
            )
        VAR _as =
            SUMX (
                FILTER (
                    merge,
                    [Month] = EARLIER ( [Month] )
                        && [Product] = EARLIER ( [Product] )
                        && [Source] = "Actuals"
                ),
                [Sales]
            )
        RETURN
            IF ( _fs = BLANK (), _as, _fs )
    )
RETURN
    IF (
        ISINSCOPE ('Products'[Product description]),
        IF ( ISBLANK ( [Forecast Sales] ), [Actual Sales], [Forecast Sales] ),
        SUMX ( summary, [FY] )
    )

Regards,

Xiaoxin Sheng

[AllisonKennedy]

@paliman  Sorry, can you please explain further detail on your problem. What does the data model data look like? Your two screenshots are in very different formats, so which one is it that you're working with in Power BI, then we can use that to help you get the result you want.

I don't understand what the problem is highlighted in red- it does exactly what you've asked in the formula, so please explain further what you're trying to do so we can help more.