Sample problem:<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
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))))<\/p>\n\n\n\n
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.<\/strong>“.\u00a0 This seems pretty simple but I can’t seem to make it work:<\/p>\n\n\n\n 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])<\/p>\n\n\n\n Priorbusinessday = if(weekday(today(),1)=1,today() – 2, if(WEEKDAY(today(),1)=2,today()-3, today()-1))<\/p>\n\n\n\n Is there a more elegant way to use the Priorbusinessday measure or will I have to use the first method?<\/p>\n\n\n\n <\/p>\n\n\n\n Solutions:<\/p>\n\n\n\n Setting the measure equal to a variable first. Because ‘Calculate” is not accepting a true\/false expression as a filter.<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" Sample problem: 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() – Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[621],"tags":[1088,1087],"class_list":["post-3970","post","type-post","status-publish","format-standard","hentry","category-power-bi","tag-calculate-has-been-used-in-a-true-false-expression-2","tag-calculate-not-accepting-a-true-false"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3970"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3970\/revisions"}],"predecessor-version":[{"id":3971,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3970\/revisions\/3971"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Prior Day Invoiced =\nVAR PreviousBusinessDay = [Priorbusinessday]\nRETURN\n CALCULATE (\n SUM ( 'Sales Ledger Transactions'[Net Sales] ),\n 'Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = \"INVOICED\",\n 'Sales Ledger Transactions'[Date.Date] = PreviousBusinessDay\n )<\/em><\/pre>\n\n\n\n