Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

  • AlB's avatar
    AlB
    6 years ago

    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

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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!