I think the answer is sort of!
In the attached I've first created a Global Filter, so you can filter to all the dates to be compared (it's important that this is a context filter). Next is just the yyyymm field where everything ticked will be selected and everything not ticked will be comparison
We then use this formula to get the comparisons
This works as FIXED LoDs are computed before any regular filters, so the FIXED LoD gives all (in context) sales, and takes away the selected sales (which as a regular aggregate, SUM, is affected by LoDs)
Hope that helps and makes sense.
Dynamic Date Comparisons.twbx 23.5 KB
I am trying to have ability to select maybe month 1,2,3 and subtract from 4,5,6 .
So if I set it up so we have the first 3 months selected it looks like this
and this reflects this
As you said you wanted to compare any month combinations this method lets you compare everything that is selected with everything that isn't. If that's not what you wanted, let me know how you want it to work and I'll take a look
What you presented is an option .
what if we selected only 2 months and compared with 2 months and didn't select the others
[1,2]-[3,4] didn't select [5,6]
I am only posting very small part of the monthyyy data. the actual data contains data from 2014 till now
So we use the Global Filter to restrict the data down to the set of dates (both selected and comparison) and then by either ticking (or unticking) tells it how we want to construct the 2 groups to compare
If we "know" how the comparison would work (eg. If I select 2017-04 I always want to compare it to 2016-04) then we don't need to use this (slightly convoluted) double filter, but this way lets you compare anything to anything (and is dynamic, as in you won't need to change the parameter list of values when the data updates, if we'd gone down that route)
Hey Thank you, it works.