cancel
Showing results for
Did you mean:
Frequent Visitor

## A function 'CALCULATE' has been used in a True/False expression - Simple Date comparison

Hi There,

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: A function 'CALCULATE' has been used in a True/False expression - Simple Date comparison

@nps59718

Try this one

Prior Day Invoiced =
RETURN
CALCULATE (
SUM ( 'Sales Ledger Transactions'[Net Sales] ),
'Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = "INVOICED",
)
4 REPLIES 4
Super User

## Re: A function 'CALCULATE' has been used in a True/False expression - Simple Date comparison

@nps59718

Try this one

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

## Re: A function 'CALCULATE' has been used in a True/False expression - Simple Date comparison

Thanks for your help Zubair - it worked well!

Regular Visitor

## Re: A function 'CALCULATE' has been used in a True/False expression - Simple Date comparison

@Zubair_Muhammad Nice solution setting the measure equal to a variable first. Works around Calculate not accepting a true/false expression as a filter very nicely  Thank you!

Occasional Visitor

## Re: A function 'CALCULATE' has been used in a True/False expression - Simple Date comparison

Worked for hours on this problem before I found this solution. Thank you @Zubair_Muhammad !

Any help explaining why it wouldn't work without creating the variable? I still don't understand the error fully.

rq

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 111 members 1,459 guests
Recent signins: