Calculate sum with multiple "AND" conditions

[dw700d]

I have two tables seperate tables shown below. An expense table and a revenue table. Both tables have a relationship based on the circuit column. I would like to create a measure that  calculates the total  Revenue  when

Expense does not = 0

and Max Month = December

and  Min Month = January

and Identifier = Y

Expense Table
Circuit	Min  Month	Max Month	Expense
A	January	December	0
B	January	January	10
C	January	December	0
D	January	December	20
E	April	June	0
F	April	December	40
G	April	December	50
H	January	April	10
I	January	May	20
J	January	December	30
K	January	December	40
L	January	July	50
M	January	December	40

Revenue Table
Circuit	Revenue	Identifier
A	100	Y
B	120	Y
C	200	Y
D	300	Y
E	80	Y
F	100	Y
G	20	Y
H	40	Y
I	60	Y
J	100	Y
K	200	Y
L	20	Y

[Greg_Deckler]

@dw700d Try:

Measure = 
    VAR __Table = FILTER( 'Revenue', [Identifier] = "Y" && RELATED(Expense[Expense]) <> 0 && RELATED(Expense[Max Month]) = "December" && RELATED(Expense[Min Month]) = "January")
    VAR __Result = SUMX(__Table, [Revenue])
RETURN
    __Result

[dw700d]

@Greg_Deckler  thank you. When I enter the related function intellisense does not give me an option to select a column. The revenue table has unique values and the expense table is the many side of the relationship. Maybe thats why but I am not sure. Any other thoughts?

[Greg_Deckler · Accepted solution]

@dw700d Well, that makes a big difference. Try this:

Measure = 
    VAR __Table = FILTER('Expense', [Expense] <> 0 && 'Expense'[Max Month] = "December" && 'Expense'[Min Month] = "January")
    VAR __Circuits = DISTINCT(SELECTCOLUMNS(__Table, "__Circuits", [Circuit]))
    VAR __Result = SUMX(FILTER('Revenue', [Identifier] = "Y" && [Circuit] IN __Circuits),[Revenue])
RETURN
    __Result

[saurabhkudale96]

Hello @dw700d ,

You can try the below solution. Please upvote if it works for you.

Total Revenue = CALCULATE( SUM(Revenue[Revenue]), Expense[Expense] <> 0, Revenue[Identifier] = "Y", DATESBETWEEN( Revenue[Circuit], FILTER( Expense, Expense[Min Month] = "January" ), FILTER( Expense, Expense[Max Month] = "December" ) ) )