cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 261 members 2,962 guests
Please welcome our newest community members: