Change <interval> enumeration in DATEADD, etc according to filter context

[JPennington]

Hello there,

Does anyone know if there's any way to adjust the <interval> argument of functions like DATEADD according to filter context? The argument takes enumerations, not text strings or numbers that would be easily swapped with some IF logic.

At the moment, I'm doing things like this:

=IF(ISFILTERED(Calendar[MthYr]),
CALCULATE([mLTD],
ALL(luItem),
luItem[CFGroup]<>0,
DATEADD(Calendar[Date],-1,month)
),
CALCULATE([mLTD],
ALL(luItem),
luItem[CFGroup]<>0,
DATEADD(Calendar[Date],-1,year)
)
)

It just seems such an inelegant solution, repeating the entire CALCULATE function just to swap the interval argument over. I've tried an intermediate measure to return year or month, but while DATEADD etc demand the intervals without double-quotes, nothing else will accept naked text as a potential return. I wondered if, similar to Excel's booleans, there might be numerical equivalents I could use but I haven't had any luck trying.

I feel like I'm being a right knucklehead, surely there must be a way to toggle that argument without rewriting the entire thing!?

Any ideas - or even any thoughts about why that interval argument is always an enumeration instead of text (they must have a reason, right?) - would be much appreciated.

Cheers!

 

[Tianbas]

This looks really strange and I wasnt aware of that. So far I only needed to change the number of periods I want to go back and thats easy (e.g. with VALUES) but never touched the last argument.

I played around a bit and also could only enter month and year directly. If I try to use a function to add the last argument It says --> "The last argument must be one of these keywords: DAY, MONTH, QUARTER, or YEAR."