Conditional Sum for Certain Values in a Column

[Anonymous]

Hi all, I've been trying to work on a conditional sum for certain values of a column, but I'm having some trouble getting to work correctly. 

This is essentially what my data looks like: 

Forecast Start Date Value Date Business Division 1 Business Division 2 Business Division 3 Value Type Group Value Type Value

3/31/2019	3/31/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Base Fee	187
3/31/2019	6/30/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Base Fee	208
3/31/2019	9/30/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Base Fee	490
3/31/2019	12/31/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Base Fee	482
3/31/2019	3/31/2020	Investment Banking	IB USA	IB US Fund 1	Revenue	Base Fee	169
3/31/2019	6/30/2020	Investment Banking	IB USA	IB US Fund 1	Revenue	Base Fee	332
3/31/2019	3/31/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Performance Fee	375
3/31/2019	6/30/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Performance Fee	439
3/31/2019	9/30/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Performance Fee	351
3/31/2019	12/31/2019	Investment Banking	IB USA	IB US Fund 1	Revenue	Performance Fee	173
3/31/2019	3/31/2020	Investment Banking	IB USA	IB US Fund 1	Revenue	Performance Fee	381
3/31/2019	6/30/2020	Investment Banking	IB USA	IB US Fund 1	Revenue	Performance Fee	445
3/31/2019	3/31/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Assets Under Management	359
3/31/2019	6/30/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Assets Under Management	121
3/31/2019	9/30/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Assets Under Management	401
3/31/2019	12/31/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Assets Under Management	197
3/31/2019	3/31/2020	Investment Banking	IB USA	IB US Fund 1	Balance	Assets Under Management	187
3/31/2019	6/30/2020	Investment Banking	IB USA	IB US Fund 1	Balance	Assets Under Management	220
3/31/2019	3/31/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Net Cash Flow	482
3/31/2019	6/30/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Net Cash Flow	396
3/31/2019	9/30/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Net Cash Flow	393
3/31/2019	12/31/2019	Investment Banking	IB USA	IB US Fund 1	Balance	Net Cash Flow	431
3/31/2019	3/31/2020	Investment Banking	IB USA	IB US Fund 1	Balance	Net Cash Flow	424
3/31/2019	6/30/2020	Investment Banking	IB USA	IB US Fund 1	Balance	Net Cash Flow	360

The data is of Revenue and Balance forecasts. The forecasts predict 5 Quarters worth of future data. The issue arises when I'm trying to put these forecasts into a matrix visualization. 

I have a slicer for the Value Type field. Same thing for the year. When I select "Assets Under Management", I would like for only the End-Of-Year value to be displayed. This is because Assets Under Management is a balance. It can't be summed up like the other values can be, because that wouldn't make sense. I would also like the Net Cash Flow values for the year to be added up into the End-Of-Year AUM if both "Net Cash Flow" and "Assets Under Management" are selected. 

For Revenue values, it's a simple CALCULATE(SUM()). But it's the Balance values that are giving me trouble. Does anyone know how I would write this measure to include that logic? What I tried doing is writing a SWITCH() and having the CALCULATE(SUM()) function be filtered for only MONTH(Revenue Date) = 12 if SELECTEDVALUE of Value Type is AUM, but that didn't work. That also doesn't take account for what happens when we're looking at an End-Of-Forecast Balance value. By that I mean, what if we select the year 2020? There's no forecast for December (Q4) in that year, the forecast ends on Q2. It makes sense to display that as the "End-Of-Year" balance, but I'm not sure how to do that. 

I'd appreciate any insights. Thank you!

[Greg_Deckler · Accepted solution]

@Anonymous You can essentially do a SUMIF using the filter clause of CALCULATE (you don't generally need to wrap a simple SUM in CALCULATE without a filter clause btw). Like CALCULATE(SUM(), [Date] = DATE(2022,12,31)) or you can do it like this:

MAXX(FILTER('Table',[Date] = DATE(2022,12,31),[some column])

If you are going to get a single row returned from the filter then any X aggregator will do, MAXX, MINX, SUMX, etc.