How to compare a value from the selection to another value which is not selected.

[#1]

Hi there,

I have a list box with dates.

I want to calculate a value based on the selected date and last date. E.g. If a user selects 01/11/2010, I have to calculate previous date's balance whcih is the balance on 15/10/2010.

I am not sure how to really do it.. any help would be great.

Much Appreciated.

[#2]

Hi,

If your first expression is sum(SALES)

The second should be sum({<[Statement Date]={'$(=date(max({1}[Statement Date])))'}>} SALES)

Regards

Benoît

[#3]

Hi Bcuisiniere,

Thanks for your reply, it does work but Im not sure I want the maximum date in the list. I want the maximum date less than selected statement date. Can you please also help me the label part. I want it to be

Previous Balance [Date]

where Date will be previous statement date.

ta.

[#4]

Hi

I made a mistake between 15/12/2010 and 15/10/2010.

then try sum({<[Statement Date]={'$(=date(max({1<[Statement Date]={"<$(=date(only([Statement Date])))"}>}[Statement Date])))'}>} SALES)

and the label

='Previous Balance '& date(max({1<[Statement Date]={"<$(=date(only([Statement Date])))"}>}[Statement Date]))

[#5]

Hi mate,

Sorry was on holiday for a couple of days. It still doesn't seem to work. 😞

[deepakk]

HI,

I am not sure if it is a Correct approach but the attach application servers the purpose.

[#7]

use peek function in the script

make

Load

Date as currentdate,

peek(Date) as previousdate

From xxx.qvd;

And use priviousdate in the chart to caculate

[#8]

Hi Rishi,

Thanks for your reply.

I implemented the peak function and it corrects returns the previous statement.

However, I am unable to use it in my expression.

I am using:

=Sum({$<StatementDate={$(=PreviousStatementDate)}>} Balance)

Please let me know if I am missing something.

Thanks,

WB

[johnw]

I can explain in more detail if needed, but this is probably the fifth time today I've suggested an AsOf table to solve similar problems, and I'm wearing out. See attached solution. Hopefully it's what you're looking for.