Only return data from forecast table if there is no corresponding data in a actuals table

[Anonymous]

Hi

I have a data table with forecast data and one with actuals.

Sometimes they overlap (for example when i recive the actuals for October but we have not forecasted from November yet - I have both actuals and forecast for October...)

In that case I just want to exclude data for October from the forecast table - "the actual data overrides the forecast"

I have tried this approch

Meassure calculates the last month of actuals

Last actual date = lastdate(Actuals[Date])

Messure calculates forecast if date is lower than year end and bigger than "last actual date"

Forecast remaining of year = calculate(sum(Forecast[Sum of Quantity Ton]);and(forecast[date]>=[Last actual date];forecast[date]<=date(year(today());12;31)))

But I get this "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

I dont get it?

Please can you give me a hand? Or is there a smarter way of doing this? 🙂

Thanks

Martin

[AlB · Accepted solution]

Hi @Anonymous 

It complains because you are using the simplified syntax for the filter with something that is not allowed. To solve that issue ( and that issue only, I haven't looked at the rest), try:

Forecast remaining of year =
CALCULATE (
    SUM ( Forecast[Sum of Quantity Ton] );
    FILTER (
        ALL ( forecast[date] );
        AND (
            forecast[date] >= [Last actual date];
            forecast[date] <= DATE ( YEAR ( TODAY () ); 12; 31 )
        )
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

---

@AlB wrote:

Hi @Anonymous 

It complains because you are using the simplified syntax for the filter with something that is not allowed. To solve that issue ( and that issue only, I haven't looked at the rest), try:

Forecast remaining of year =
CALCULATE (
    SUM ( Forecast[Sum of Quantity Ton] );
    FILTER (
        ALL ( forecast[date] );
        AND (
            forecast[date] >= [Last actual date];
            forecast[date] <= DATE ( YEAR ( TODAY () ); 12; 31 )
        )
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

---

Worked like a charm!

Great suport

Thanks alot AIB!