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

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

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).

 Month Posted Values Plan Values 1 352 200 2 194 200 3 0 200 4 170 200 5 200 6 200 Total 717 800

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???).

• ###### Re: How do I get the most recent posting date for a measure?

Try with

Only({\$ <[Posted Values]={'\$(=Max([Posted Values]))'}>} Month)

let me know

• ###### Re: How do I get the most recent posting date for a measure?

=Only({\$<Month={\$(=Max({\$<[Posted Values]-={}>}Month))}>}[Posted Values])

Results: 170

• ###### Re: How do I get the most recent posting date for a measure?

=SUM({<Month = {"=SUM([Posted Values] > 0)"}>}[Plan Values])

• ###### Re: How do I get the most recent posting date for a measure?

Well that certainly seems to work (thanks!).

I still can't totally get my head around Set Analysis, so I'm not totally sure why that should work (I suppose that {<Month = {"=SUM([Posted Values] > 0)"}>} is acting like a WHERE statement.

In any case, I'll bookmark this because I fear it will come in useful in the future.

• ###### Re: How do I get the most recent posting date for a measure?

=SUM({<Month = {"=SUM([Posted Values] > 0)"}>}[Plan Values])

Month = {"=SUM([Posted Values] > 0)"}

This will consider on those months where PostedValue is higher than 0...

• ###### Re: How do I get the most recent posting date for a measure?

A-ha - things are becoming clearer now.  Curly-brackets = "WHERE" (I think I've read something similar elsewhere).

Thanks again.