sum a value based on finding the most recent date

[#1]

I have a stock value saved for a number of weeks (aligned in the table with the "week beginning" date). Every week a new value is added with a new date.

I am trying to produce an expression that will always pick the most recent weeks value to display...and this is what I have so far:

=Sum({$<Date={$(=LatestDate)}>}[Value (USD)])

where Latest Date is a variable representing the formula MaxString(Date).

I cannot get this to produce a result even though the variable on its own in a text box returns the latest date OK...

Can somebody please point me in the right direction.

Thanks......

[swuehl]

Have you tried enclosing your date into single quotes:

=Sum({$<Date={'$(LatestDate)'}>}[Value (USD)])

[#3]

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

[#4]

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

[#5]

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

[#6]

Thanks for the prompt reply:

Just gave it a go with no luck....

If I type the actual date between the 2 quotes, I get the correct values returned. It just seems to be the way the formula is interacting with the variable....

[swuehl]

If you remove the label from your chart expression, QV will show the expression itself, with dollar sign expansion being evaluated. What do you see?

[#8]

With my original expression I see “-“. With the variation you have suggested I see the value $0.....(which I guess means the formula syntax is correct, but is not returning the value I expect)....

Getting closer, but I am still stumped as to why the variable on its own returns the value 8/7/2013, if I input 8/7/2013 into the formula, I get the value I am expecting, but putting the 2 together gives me $0.....

[swuehl]

Have you tried the variable expansion without the equal sign as shown above? And, how does your expression show up exactely in the label? I assume your variable 8/7/2013 is evaluated as numeric expression, so it will return a number near zero.

[#10]

Thanks for the continued help.....I'll try to cover as many bases here as possible:

If I enter the expression: =$(LatestDate) - I get 08/07/2013

If I enter: =Sum({$<Date={'08/07/2013'}>}[Value (USD)]) - I get $15,000 (ish)

If I enter: =Sum({$<Date={'$(LatestDate)'}>}[Value (USD)]) - I get $0

If I enter =Sum({$<Date={$(=LatestDate)}>}[Value (USD)]) - I get just - (a dash)