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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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."
 
Upvote 0

Forum statistics

Threads
1,226,534
Messages
6,191,621
Members
453,667
Latest member
JoeH7745

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top