6 Replies Latest reply: Oct 22, 2015 5:31 AM by Simon Hogg RSS

    How do I get the most recent posting date for a measure?

    Simon Hogg

      I have the following data.  How do I get the most recent month which has posted values in a formula? (Unfortunately it doesn't correspond to the current or prior month in a nice fashion).

        

      MonthPosted ValuesPlan Values
      1352200
      2194200
      30200
      4170200
      5200
      6200
      Total717800

       

      In English, I want to say, "Give me the sum of the plan values for all the months up to the most recent month with Posted Values".

       

      I can't just use Max (Month), because that will give June.  I tried Max (if ([Posted Values] > 0 , Month , 0)) but that just gave 0.  I think my confusion is that I'm trying to use Month as a measure as well as a dimension for aggregation.

       

      Is this a job for set analysis, or should it be fixed in the load script (and if so, how???).