Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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 )
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.
@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!
Thanks for your help Zubair - it worked well!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |