=If(Max(Date)<='2/28/2023', avg([Profit]),
If(Min(Date)>='3/1/2023', (Revenue-Expense),
If(COUNT(DISTINCT Date)>1 OR IsNull(Date), Avg([Profit]), (Revenue-Expense)
I have two data sets. The first one is a set of records containing profit from December to March. The second one with a set of records containing Revenue and Expenses from February to April.
With my current expression, for any month prior to March, I pull the profit value for the given month/(s). For any month after February, I calculate (Revenue- Expense). My current expression solves the first half of my problem. However, when I don't select any month, I should be able to average both the profit values and the (revenue-expense) values together as one. The rule being only the profit values prior to March, along with only (revenue-expense) values after February.
For example, if I were to not filter by any specific month, then I would just get an average of all the values. However, the profit value for March should not be considered in the calculation of averages. What should be considered in the calculation is the (revenue-expense) for march. Alternatively, the (revenue-expense) for February should not be considered if we are calculating the averages for all values. What should be considered is the profit value for March.
Please see the sample data attached.
