EAC - Calculate different series based on actuals availability

[JMHenriques]

Hi everyone,

So... I have a table with a product that has 2 series, actual cost and forecasted cost.

Month	Product	Serie	Cost
January	XYZ	Forecast	311
January	XYZ	Actual	465
February	XYZ	Forecast	357
February	XYZ	Actual	167
March	XYZ	Forecast	372
March	XYZ	Actual	112
April	XYZ	Forecast	457
April	XYZ	Actual	0
May	XYZ	Forecast	314
May	XYZ	Actual	0
June	XYZ	Forecast	486
June	XYZ	Actual	0
July	XYZ	Forecast	179
July	XYZ	Actual	0
August	XYZ	Forecast	496
August	XYZ	Actual	0
September	XYZ	Forecast	280
September	XYZ	Actual	0
October	XYZ	Forecast	123
October	XYZ	Actual	0
November	XYZ	Forecast	245
November	XYZ	Actual	0
December	XYZ	Forecast	345
December	XYZ	Actual	0

I want to estimate the EAC (estimation at completion) that is the actuals when available + the forecast for the rest.

It should be something like SUM of the actuals plus the forecast of the remaining months when actual = 0... but I cannot get to a solution that works... 

any help from the experts? 🙂

[Ashish_Mathur]

Hi,

Clearly show the expected result in a simple Table format.  Please share a revised table with a Year column as well.

[v-xinruzhu-msft · Accepted solution]

Hi @JMHenriques 

You can refer to the following measure

EAC =
VAR _sumavailableactual =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Product]
                IN VALUES ( 'Table'[Product] )
                    && [Serie] = "Actual"
        ),
        [Cost]
    )
VAR _availableforecast =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Product]
                IN VALUES ( 'Table'[Product] )
                    && [Serie] = "Actual"
                    && [Cost] = 0
        ),
        [Month]
    )
VAR _sumfor =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Product]
                IN VALUES ( 'Table'[Product] )
                    && [Month] IN _availableforecast
        ),
        [Cost]
    )
RETURN
    _sumavailableactual + _sumfor

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.