Calculated Column removing filter context

Question

Hello -&nbsp;&nbsp;I am trying to create a calculated column that will provide me the SUM of the cost based on a specific date value for the row in my table.&nbsp;I have 4 main tables:Wireless_Numbers, Date_Table, Overview of Lines, Monthly_Costs with the below sample data:&nbsp;Wireless Numbers:idWirelessnumber1xxxxxxx2xxxxxxx3xxxxxxx4xxxxxxx5xxxxxxx6xxxxxxx7xxxxxxx&nbsp;Overview of Lines (Cost is the column i want to calculate):numberidwirelessnumberBill Cycle DateActivation DateBenchmark Cycle DateCost1xxxxxxx1/1/20221/1/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32.002xxxxxxx1/1/20221/2/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.003xxxxxxx1/1/20221/3/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33.004xxxxxxx1/1/20221/4/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33.005xxxxxxx1/1/20221/5/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23.006xxxxxxx1/1/20221/6/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26.001xxxxxxx2/1/20221/1/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32.002xxxxxxx2/1/20221/2/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.003xxxxxxx2/1/20221/3/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33.004xxxxxxx2/1/20221/4/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33.005xxxxxxx2/1/20221/5/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23.006xxxxxxx2/1/20221/6/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26.007xxxxxxx2/1/20222/12/20222/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32.001xxxxxxx3/1/20221/1/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32.002xxxxxxx3/1/20221/2/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.003xxxxxxx3/1/20221/3/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33.004xxxxxxx3/1/20221/4/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33.005xxxxxxx3/1/20221/5/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23.006xxxxxxx3/1/20221/6/20191/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26.007xxxxxxx3/1/20222/12/20222/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32.00&nbsp;Monthly_CostnumberidwirelessnumberBill Cycle DateCost CategoryCost1xxxxxxx1/1/2022Data Usage172xxxxxxx1/1/2022Data Usage173xxxxxxx1/1/2022Data Usage164xxxxxxx1/1/2022Data Usage165xxxxxxx1/1/2022Data Usage136xxxxxxx1/1/2022Data Usage141xxxxxxx2/1/2022Data Usage122xxxxxxx2/1/2022Data Usage153xxxxxxx2/1/2022Data Usage164xxxxxxx2/1/2022Data Usage185xxxxxxx2/1/2022Data Usage166xxxxxxx2/1/2022Data Usage187xxxxxxx2/1/2022Data Usage201xxxxxxx3/1/2022Data Usage172xxxxxxx3/1/2022Data Usage153xxxxxxx3/1/2022Data Usage184xxxxxxx3/1/2022Data Usage125xxxxxxx3/1/2022Data Usage146xxxxxxx3/1/2022Data Usage147xxxxxxx3/1/2022Data Usage111xxxxxxx1/1/2022Fees and Taxes152xxxxxxx1/1/2022Fees and Taxes143xxxxxxx1/1/2022Fees and Taxes174xxxxxxx1/1/2022Fees and Taxes175xxxxxxx1/1/2022Fees and Taxes106xxxxxxx1/1/2022Fees and Taxes121xxxxxxx2/1/2022Fees and Taxes112xxxxxxx2/1/2022Fees and Taxes103xxxxxxx2/1/2022Fees and Taxes104xxxxxxx2/1/2022Fees and Taxes125xxxxxxx2/1/2022Fees and Taxes136xxxxxxx2/1/2022Fees and Taxes157xxxxxxx2/1/2022Fees and Taxes121xxxxxxx3/1/2022Fees and Taxes202xxxxxxx3/1/2022Fees and Taxes133xxxxxxx3/1/2022Fees and Taxes204xxxxxxx3/1/2022Fees and Taxes155xxxxxxx3/1/2022Fees and Taxes206xxxxxxx3/1/2022Fees and Taxes157xxxxxxx3/1/2022Fees and Taxes15&nbsp;Date Table is standard date dimensional table that is joined to the fact tables on Bill Cycle Date.&nbsp;What I am looking to do is calculate the&nbsp;COST&nbsp;from the Monthly_cost table for the Benchmark Date associated with each Wireless number.&nbsp; So even if we are looking at March 2022 reporting, I want to see what our cost was for January 2022 for the wirelessid 1, 2 etc.&nbsp;&nbsp;&nbsp;We need to compare our benchmark costs, versus our go forward monthly costs to see the monthly savings we are generating for each wireless number.&nbsp;Is there a way to essentially create this SUMIF excel statement in Power BI:SUMIFS(Cost, wireless number (Monthly Cost) = Wireless Number (row in Overview of Lines) AND Bill Cycle Date (Monthly Cost) = Benchmark Date (Overview of lines).&nbsp;I have been trying to figure out this solution with no luck so any help would be great!ThanksRyan

anonymous · Accepted Answer

Hi&nbsp;ryan_b_fiting&nbsp;,
Please refer to my pbix file to see if it helps you.
Create relationships between tables.

Create a column.
Column =
CALCULATE (
    SUM ( 'monthly cost'[Cost] ),
    FILTER (
        'monthly cost',
        'monthly cost'[wirelessnumber] = EARLIER ( 'Overview of Lines'[wirelessnumber] )
            &amp;&amp; 'monthly cost'[Bill Cycle Date]
                = EARLIER ( 'Overview of Lines'[Benchmark Cycle Date] )
    )
)

Or a measure.
Measure_RE =
CALCULATE (
    SUM ( 'monthly cost'[Cost] ),
    FILTER (
        ALL ( 'monthly cost' ),
        'monthly cost'[wirelessnumber]
            = SELECTEDVALUE ( 'Overview of Lines'[wirelessnumber] )
            &amp;&amp; 'monthly cost'[Bill Cycle Date]
                = SELECTEDVALUE ( 'Overview of Lines'[Benchmark Cycle Date] )
    )
)

How to Get Your Question Answered Quickly&nbsp;
&nbsp;
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
&nbsp;
Best RegardsCommunity Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

Forum Discussion

Calculated Column removing filter context

2 Replies

Related content

Remove visual filter context

Remove Row context without removing Filter Context

Removing Filter Context with All

Remove Row Context in Calculated Column

Removing filter context

id	Wirelessnumber
1	xxxxxxx
2	xxxxxxx
3	xxxxxxx
4	xxxxxxx
5	xxxxxxx
6	xxxxxxx
7	xxxxxxx

numberid	wirelessnumber	Bill Cycle Date	Activation Date	Benchmark Cycle Date	Cost
1	xxxxxxx	1/1/2022	1/1/2019	1/1/2022	32.00
2	xxxxxxx	1/1/2022	1/2/2019	1/1/2022	31.00
3	xxxxxxx	1/1/2022	1/3/2019	1/1/2022	33.00
4	xxxxxxx	1/1/2022	1/4/2019	1/1/2022	33.00
5	xxxxxxx	1/1/2022	1/5/2019	1/1/2022	23.00
6	xxxxxxx	1/1/2022	1/6/2019	1/1/2022	26.00
1	xxxxxxx	2/1/2022	1/1/2019	1/1/2022	32.00
2	xxxxxxx	2/1/2022	1/2/2019	1/1/2022	31.00
3	xxxxxxx	2/1/2022	1/3/2019	1/1/2022	33.00
4	xxxxxxx	2/1/2022	1/4/2019	1/1/2022	33.00
5	xxxxxxx	2/1/2022	1/5/2019	1/1/2022	23.00
6	xxxxxxx	2/1/2022	1/6/2019	1/1/2022	26.00
7	xxxxxxx	2/1/2022	2/12/2022	2/1/2022	32.00
1	xxxxxxx	3/1/2022	1/1/2019	1/1/2022	32.00
2	xxxxxxx	3/1/2022	1/2/2019	1/1/2022	31.00
3	xxxxxxx	3/1/2022	1/3/2019	1/1/2022	33.00
4	xxxxxxx	3/1/2022	1/4/2019	1/1/2022	33.00
5	xxxxxxx	3/1/2022	1/5/2019	1/1/2022	23.00
6	xxxxxxx	3/1/2022	1/6/2019	1/1/2022	26.00
7	xxxxxxx	3/1/2022	2/12/2022	2/1/2022	32.00