New Table: IF x is true, then CALCULATETABLE a, else CALCULATETABLE b

[Anonymous]

Hey all,

I've got what I thought would be a straightforward DAX expression, but it's apparently not... I want it to evaluate today's date, and if today is before November 1, then calculate a table with certain filters applied, otherwise apply different filters. Independently, each version of the CALCUALTETABLE works; it's just when I roll it into the IF statement that it errors. I will probably just have to figure out a different way to achieve the model I'm looking for, but on the off chance I'm missing something simple, have a look. The error I'm getting is, "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

New Table =

if(today()=date(year(today()),11,1),

CALCULATETABLE(
'Sales Order',
'Sales Order'[OrderApi__Date__c]>= DATE(2017,11,9),
'Sales Order'[OrderApi__Date__c]<= DATE(2018,10,31))
,

CALCULATETABLE(
'Sales Order',
'Sales Order'[OrderApi__Date__c]>= DATE(2017,11,9),
'Sales Order'[OrderApi__Date__c]<= DATE(2018,month(today()),day(today()))
)
)

Thanks for the help!

[Greg_Deckler]

That's actually kind of weird. But, you can solve it like this:

New Table =

VAR filter1 = DATE(2017,11,9)
VAR filter2a = DATE(2018,10,31)
VAR filter2b = DATE(2018,month(today()),day(today()))
VAR filter2 = if(today()<date(year(today()),11,1),filter2a,filter2b)

RETURN 

CALCULATETABLE(
'Sales Order',
'Sales Order'[OrderApi__Date__c]>= filter1,
'Sales Order'[OrderApi__Date__c]<= filter2)