cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nps59718 Frequent Visitor
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?

 

Thanks in Advance.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@nps59718

 

Try this one

 

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
    )
4 REPLIES 4
Super User
Super User

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

@nps59718

 

Try this one

 

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
    )
nps59718 Frequent Visitor
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!

sphillips22 Regular Visitor
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 Smiley Happy Thank you!

analyticsrq Occasional Visitor
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

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 111 members 1,459 guests
Please welcome our newest community members: