Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

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  Datanaut

View solution in original post

2 REPLIES 2
AlB
Community Champion
Community Champion

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  Datanaut

Anonymous
Not applicable


@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  Datanaut


Worked like a charm!

Great suport

Thanks alot AIB! 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.