PowerPivot calculated item: calculate only for prior months

[cr731]

I have a calculated field in my PowerPivot model that takes Actuals minus Budget to equal Variance. This value is displayed by month. However, when the month is in the future, I don't want this to show anything because it's not valid to subtract a zero Actuals when there are no Actuals yet.

I'm drawing a blank on how to accomplish this.

 

[olivierhbh]

Can you try this formula?

IF (
    [date] > TODAY ()
        || (
            MONTH ( [date] ) = MONTH ( TODAY () )
            && YEAR ( [date] ) = YEAR ( TODAY () )
        ),
    BLANK (),
    [actuals] - [budget]
)

Let me know if it helps.

Olivier.

 

[cr731]

Can you try this formula?

IF (
    [date] > TODAY ()
        || (
            MONTH ( [date] ) = MONTH ( TODAY () )
            && YEAR ( [date] ) = YEAR ( TODAY () )
        ),
    BLANK (),
    [actuals] - [budget]
)

Let me know if it helps.

Olivier.

I get an error that "The value for 'Period' cannot be determined. Either 'Period' doesn't exist, or there is no current row for a column named 'Period'."

('Period' refers to the "date" column, and contains date values)