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

JPennington

New Member
Joined
Aug 6, 2014
Messages
1
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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
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."
 

Watch MrExcel Video

Forum statistics

Threads
1,100,200
Messages
5,473,121
Members
406,846
Latest member
otown2221

This Week's Hot Topics

Top