Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
nps59718
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
Zubair_Muhammad
Community Champion
Community Champion

@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
    )

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

@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
    )

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

Same here. 

 

Any elaboration is appreciated.

Anonymous
Not applicable

@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!

Thanks for your help Zubair - it worked well!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.