Excluding values in a filter but keeping others needed for calculation

[johnf]

I'm stuck and appreciate any advice from the community.

I have the following tables - 

INCOME

No of Months Since Start	Client	Income
1	Bob Smith	$1000
8	Bob Smith	$2500
13	Bob Smith	$1000
17	Bob Smith	$1500
22	Bob Smith	$1900
5	Simon Says	$500
9	Simon Says	$1500

BUDGET

No of Months Predicted	Client	Budget
4	Bob Smith	$1000
12	Bob Smith	$9000
19	Bob Smith	$5000
26	Bob Smith	$2500
33	Bob Smith	$3000
8	Simon Says	$1500
12	Simon Says	$3000
19	Simon Says	$5000
27	Simon Says	$3500

JOINING TABLE

Months	Named Period
0	0 Months
1	0 Months
2	0 Months
3	0 Months
4	0 Months
5	0 Months
6	6 Months
7	6 Months
8	6 Months
9	6 Months
10	6 Months
11	6 Months
12	1 year
13	1 year
14	1 year
15	1 year
16	1 year
17	1 year
18	1.5 years
19	1.5 years
20	1.5 years
21	1.5 years
...	....

The INCOME and BUDGET tables are joined via the JOINING table on "No of Months Since Start" * <- 1 "Months" 1 -> * "No of Months Predicted".

What the users want to be able to select is the "Named Period" (0 months, 6 months etc) and show the sum of INCOME up to that period and the sum of BUDGET up to that period where the client is over that number of months in the INCOME and exclude those who haven't gone in to this period.

For example if the "named Period" selected was 1 year then only Bob Smith would be summed for INCOME and BUDGET as Simon Says hasn't gone past 12 months in the INCOME table yet.

I can't quite figure out how to exclude rows based on the "Named Period" without excluding rows that need to be summed.

Any help would be apprecuiated.

Cheers,

John

[v-caliao-msft]

Hi @johnf,

It's hard to understand your requirement, please provide us the details about it. In your scenario, how can you get JOINING TABLE by join INCOME and BUDGET table?

Regards,

Charlie Liao