Calculating a measure based on users input

Question

I have a requirement for a measure to be created based on users input.  The measure need to be calculated are as below:

Sum(LastPeriods(ConsumptionPeriod(QTY Issued From Store)) and Daily Rolling Run Rate * Lot Size Days

The consumptionPeriod is a column in a table by name 'Configurable Values'. The sample table is as shown below:

Configurable Values

ConfigurableValuesSK ConsumptionPeriod LeadTimeDays SafetyStockDays LotSizeDays MinimumLotSize ConsumptionDivisor

1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2
3 3 3 3 3 3 3 3
4 4 4 4 4 4 4 4
5 5 5 5 5 5 5 5
6 6 6 6 6 6 6 6
7 7 7 7 7 7 7 7
8 8 8 8 8 8 8 8
9 9 9 9 9 9 9 9
10 10 10 10 10 10 10 10

In the above table ConfigurableValuesSk is the surrogate Key. For now this table is not linked with any fact table. The requirement is that users will select the period from this table and the measure will be calculated according to the measure selected. 

For example if a user select [Lot Size Days] as 9 then the measure ( Daily Rolling Run Rate * Lot Size Days) becomes (Daily Rolling Run Rate * 9)

---

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

Answer 1

See my solution to this in this article:

http://www.powerpivotpro.com/2011/03/guest-post-nth-occurrence-dax-formula/

As far I know, this is the only way to do what you want.

David Hager

Excel FMVP