I am trying to create a variable that identifies the most recent fiscal month where I have ACTUAL cost data. This seems like it should be very straightforward but I'm not a programmer and can't figure what I'm doing wrong syntactically. I've been poking around these help pages and trying to apply the solutions but none are working. I've tried things like this:
=Max(([Fiscal Month]) WHERE Sum(ACTUAL)>0)
When that didn't work, I tried set analysis:
= Max({<SUM(ACTUAL)= {">{0"}>}[Fiscal Month])
I'm attaching a table that shows what my summarized data looks like. In this case, I want the variable to return "MAY" since that is the most recent month where the sum of the ACTUAL values exceeds zero.
I'm not using actual dates in this case. My data includes a three character text string field, [Fiscal Month]; my load statement uses the DUAL function to assign the fiscal month number, 1-12, for sorting etc. So that is how I hope to obtain the MAX(), by using that numeric dual value.
I hope I stated everything clearly enough here to make sense. Thanks a lot for your help!
