How to Fix “A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.”

Posted on by Summa Lai

Sample problem:

I’m looking for a more elegant way sum based on a prior business day calculation.  The following *does work* but will get a little more cumbersome when doing MTD or YTD calculations:

Prior Day Invoiced = CALCULATE(sum(‘Sales Ledger Transactions'[Net Sales]),’Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = “INVOICED”,
‘Sales Ledger Transactions'[Date.Date] = (if(weekday(today(),1)=1,today() – 2,
if(WEEKDAY(today(),1)=2,today()-3, today()-1))))

What I’d like to do (see below) blows up with the error “A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.“.  This seems pretty simple but I can’t seem to make it work:

Prior Day Invoiced = CALCULATE(sum(‘Sales Ledger Transactions'[Net Sales]),’Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = “INVOICED”, ‘Sales Ledger Transactions'[Date.Date] = [Priorbusinessday])

Priorbusinessday = if(weekday(today(),1)=1,today() – 2, if(WEEKDAY(today(),1)=2,today()-3, today()-1))

Is there a more elegant way to use the Priorbusinessday measure or will I have to use the first method?

Solutions:

Setting the measure equal to a variable first. Because ‘Calculate” is not accepting a true/false expression as a filter.

Prior Day Invoiced =
VAR PreviousBusinessDay = [Priorbusinessday]
RETURN
    CALCULATE (
        SUM ( 'Sales Ledger Transactions'[Net Sales] ),
        'Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = "INVOICED",
        'Sales Ledger Transactions'[Date.Date] = PreviousBusinessDay
    )