How to create a calculated field by comparing a detail value with aggregated value like a MAX.

 

If I want to generate a REVENUE field for those transactions happened yesterday, I will use:

    if datediff('day', order_date, today()) =1 then [Revenue] else NULL end

 

However,

If I want to generate a REVENUE field for those transactions happened one before the latest date of all available data, I will use:

    if datediff('day', order_date, max_date) =1 then [Revenue] else NULL end

 

   *where

         max_date is a calculated field by materializing the maximum date of the same data set (I duplicate in DATA shelf).

But field, the error message is :

        Cannot mix aggregate & non-aggregate comparisons or results in IF expression

 

Any help is highly appreciated.

thank you !!!