reuse
1 Topic
Helper function for date comparison
I have many formulas which calculate a measure. For instace: sales, itemQty, returns, returnQty etc. etc. Basiscally, they all follow the same pattern. For instance: itemSales = SUM(ob_sales[paidAmount]) In this report, I have a conditional 'last year' setup. When people select only 1 of 3 days, i need to shift back 7*52 days. When a month is selected (ie: aug 1 till aug 31), then I want to use SAMEPERIODLASTYEAR. So, the measure below is working fine: itemSalesLY = VAR SelectedDatesCount = COUNTROWS(VALUES('calendar'[Date])) VAR IsSmallSelection = SelectedDatesCount <= 27 VAR DateRangeLastYear = CALCULATE( SUM(ob_sales[paidAmount]), DATEADD('calendar'[Date], -52*7, DAY) ) VAR SalesLastYearExactDates = CALCULATE( SUM(ob_sales[BetaaldExclBtw]), SAMEPERIODLASTYEAR('calendar'[Date]) ) RETURN IF( IsSmallSelection, DateRangeLastYear, SalesLastYearExactDates ) This works like a charm, but as I said, I have many measures which need to use this LY date logic. So, I thought I'll create a lastYearDateHelper expression, but this is where it gets ugly. Whatever i try, it always ends in errors like A function ‘PLACEHOLDER’ has been used in a True/False expression that is used as a table filter expression. This is not allowed. The goal is to have something like this: itemSalesLY = CALCULATE( SUM(ob_sales[paidAmount]), [LastYearDateHelper] ) Is this even possible?Solved1.8KViews0likes6Comments