Calculating measures depending on others!

[fernanda]

Hello!

This is the problem i am trying to solve.
I´m trying to generate a report that has this behaviour.

At report time, the user must have the chance to insert a year and a month that will be the references to the values he want to consult.

In the designer, i´m thinking of do this using the prompt function but i don´t know how to acess the value he chose from other dimensions.

Then, i have 5 measures: the first that shows me the sales value for the year and month he chose as reference (how do I have this information?), the second measures that gives me the sales value for the month before of the reference month chosen by the user; and son on...

How can I do this?

Thank you

Fernanda

 

[promero]

Repeat the @Prompt(...) everywhere you need the value, even in different data providers. the user will only see the prompt once.

With DB2, when i want to maka a measure on Designer that aggregates conditionally, i use:

SUM(CASE WHEN <<condition>> THEN <<EXPRESSION>> ELSE 0 END)

On other DBs there are equivalent if/then/else constructions. This way you can make 5 compatible measures (that can be used in the same query), one for each month.

Other solution is to return a dimension with the number of months old of the data, and then at report level use:
=<sales> where (<months ago>=1)