2 Replies Latest reply on Dec 17, 2015 8:49 AM by ming.ticchione

# Using Table Calculation Results as filter while retain certain data

Hello,

I have a set of data which contains 2 different month end data.  I want to build up a comparison view between the 2-month data.  I would like to filter out dataset based on certain threshold on the difference of the 2-month values.

Here is the example.

Here is how the worksheet looks like before I apply any filter.  The MoM_Balance is the difference between Feb and Jan revenue. Please note that Jan difference is null as there is no prior revenue to calculate the difference. Say if I want to only look at the stores which has revenue difference more than 500K.  I do this by using the abs(MoM_Balance) field.    After applying the filter, now it only shows the Feb data. My question is if there is another way to calculate the difference ( or do these kinds of comparison) while retaining the records from both months ?

• ###### 1. Re: Using Table Calculation Results as filter while retain certain data

hi Ming,

Yes I'm pretty sure we can help here...the trick is to create a calculation for each of your months, and then use the measure names/values to display them how you want.

Hard coded the formulas would be

[Sales Feb] - and bring in as AGGREGATE SUM

IIF([Month] = 'Feb', [Sales],0)

[Sales Jan] - and bring in as AGGREGATE SUM

IIF([Month] = 'Feb', [Sales],0)

variance is

SUM([Sales Feb]) - SUM([Sales Jan])

Now this isn't very dynamic (you'll need to change the formulas each month). So we can create a Month Index (an use that in place of the 'Jan'/'Feb')...if you look at my post here it shows you how Re: Filter values up till current month

Hope this makes sense, but if not please post back. If you can post you workbook, I can create the calculations in there and give you an 'exact' solution.

• ###### 2. Re: Using Table Calculation Results as filter while retain certain data

Hi Simon,

Thank you for your quick response.  This is very helpful!

Ming