Forum Discussion

aflip's avatar
aflip
Frequent Visitor
3 years ago
Solved

FILTER function resulting in error - HELP

I have a dataset consisting of lease numbers, accrual periods, production dates, and volumes by product. I'm trying to do a simple FILTER() function as a measure, but am getting this error: "The expr...
Need Help
  • bsheffer's avatar
    bsheffer
    3 years ago

    change your date to 

    date(2023,01,01)
bsheffer's avatar
bsheffer
Continued Contributor
3 years ago

ok you don't need two tables.  You just have to check if a product and date existing in the prior period.  You can set your slicer to filter on the current period

 

the results I got were

 

 

measure assumes you want

1.  if the period you choose in your slicer is the earliest period you have data for, then return no rows

2.  if the product and date exist in the prior month (period) then do not show it

3.  if the product and the date do not exist in the prior month then show that row

 

_test =
var _tested_date = min('Sheet1'[Date])
var _product = min('Sheet1'[Product])
var _accrual_period_date = min('Sheet1'[Accrual_Period_Date])
var _min_all_date = calculate(min('Sheet1'[Accrual_Period_Date]), all('Sheet1'))
var _prior_date = dateadd('Sheet1'[Accrual_Period_Date], -1, MONTH)
return
if(_accrual_period_date = _min_all_date, 0,
if(calculate(COUNTROWS(Sheet1), filter(all(Sheet1), 'Sheet1'[Accrual_Period_Date] = _prior_date && 'Sheet1'[Date] = _tested_date && 'Sheet1'[Product] = _product)) = 0, 1, 0)
)
 
add this measure as a visual filter set to a value of 1
 
aflip's avatar
aflip
Frequent Visitor
3 years ago

bsheffer Thank you so much! This helped tremendously. One more question...If I want to now see the opposite...where it exists in the previous month but does not exist in the current month, what would that look like?