Filtering the View Without Filtering Underlying Data

[lingvistt]

Hi all!

I have Sales Value for 2016-2018 splitted into 13 periods in each year. I have calculated MAT, MAT-1 & MAT growth. using calculated measures

As I have data starting only from '2016 1', MAT growth is relevant only from '2017'17' (MAT 2017'13 divided by MAT 2016'13).

I need to filter the view, but avoid affecting the data (as if I affect it, my MAT, MAT-1 would change). See the sceen below what I need in 'from -> to' format:

https://drive.google.com/open?id=1sxOLTB72jv7bvKqG_1vZL2NE8WYU_lK5

[Greg_Deckler]

Would need to understand your formulas but typically in these kinds of cases, you filter the visual but in your Measure formulas, you override those filters in doing your calculations by using ALL, ALLEXCEPT, etc.

[lingvistt]

Hi Greg,

Thanks for a prompt reply! Here are the formulas I use:

1) MAT Sales = SUM (Table[Sales Value])

2) Indexed: for '2016 1' is equal to 1, for '2016 2' is equal to 2, etc.

3) MAT = SUMX( FILTER(ALLSELECTED(Table), Table[Indexed] <= MAX(Table[Indexed]) && Table[Indexed] >= MAX(Table[Indexed])-12), Table[MAT Sales])

4) MAT-1 = SUMX( FILTER(ALLSELECTED(Table), Table[Indexed] <= (MAX(Table[Indexed])-13) && Table[Indexed] >= (MAX(Table[Indexed])-12-13), Table[MAT Sales])

Greg, I already use ALLSELECTED because I need to use slicer filters to modify data in other dimensions (different from time; namely: clients, products, etc). How can I adjust the formulas to hide previous time periods without affecting calculations?

[Greg_Deckler]

So, you would filter the visual in the Filters section. Your underlying data still exists, it is just filtered out in the visual. But, as long as you write your measure formulas correctly, you can use the underlying data in your calculations. You are effectively overriding the filters on the visualization for the purposes of your calculation.

In your case you would filter your Year4wID to greater than or equal to 201713.