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!
