Remove Forecast Data From Historical Dates

[bh98381]

Here's a DAX pattern that I have used in the past to forecast revenue.  It worked great but now I'm trying to use the pattern on a different data set where there is not revenue for each day. 

Here's the DAX

$ Revenue Forecast = 
IF( 
    ISBLANK([$ Revenue]),
CALCULATE([$ Revenue 30D MA],
        SAMEPERIODLASTYEAR('Date'[Date])
         ),
BLANK())

On the other data set the ISBLANK function would remove the Forecast for historical dates.  But now since there are historical days without revenue the ISBLANK function is not working for the new data set.  

Here's an example below:

As you can see there was no revenue last year on 1/5 or 1/6 so because [$ Revenue] is blank now it calcuates a forecaste amount.

Any ideas?  Thanks!!!!

[OwenAuger · Accepted solution]

@bh98381

Here's one idea:

$ Revenue Forecast =
VAR LastRevenueDate =
    LASTNONBLANK ( ALL ( 'Date'[Date] ), [$ Revenue] )
RETURN
    CALCULATE (
        CALCULATE ( [$ Revenue 30D MA], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
        KEEPFILTERS ( DATESBETWEEN ( 'Date'[Date], LastRevenueDate + 1, BLANK () ) )
    )

The logic is:

1. Find the last date with nonblank actual revenue, and call that LastRevenueDate.
2. For all dates after LastRevenueDate, calculate forecast revenue.

'Date' should be marked as date table for this to be a reliable measure (probably already is).

Does this work in your model?

Regards,

Owen

[bh98381]

Works Perfectly, Thanks Owen!!!