Hi guys
I have a series of nested measures. One of these measures calculates daily throughout the month. However, I require this measure to be rolled up into month end periods when dragged into a pivot table and also for the purposes of other measures that rely on it (e.g. opportunity cost is calculated at month end, not daily).
Essentially, I have the below:
Sales:=SUMX( fact, [quantity] * [price] )
And I'd like to roll it up like so:
SalesByMonthEnd := SUMX ( SUMMARIZE ( dimDate, dimDate[LastDayOfMonth] ), [Sales] )
Unfortunately, when I drag [SalesByMonthEnd] into a pivot table against dimDate[Date], it still returns the daily totals, e.g.
<tbody>
</tbody>
How do I ensure it calculates daily but then always rolls up into a dimDate[MonthEnd]?
Thanks,
Simon
PS: Cross posted to msdn forums
I have a series of nested measures. One of these measures calculates daily throughout the month. However, I require this measure to be rolled up into month end periods when dragged into a pivot table and also for the purposes of other measures that rely on it (e.g. opportunity cost is calculated at month end, not daily).
Essentially, I have the below:
Sales:=SUMX( fact, [quantity] * [price] )
And I'd like to roll it up like so:
SalesByMonthEnd := SUMX ( SUMMARIZE ( dimDate, dimDate[LastDayOfMonth] ), [Sales] )
Unfortunately, when I drag [SalesByMonthEnd] into a pivot table against dimDate[Date], it still returns the daily totals, e.g.
| 7/8/2009 | 472.0 |
| 8/5/2009 | 0.2 |
| 9/2/2009 | 0.1 |
| 9/3/2009 | 0.0 |
| 10/2/2009 | 0.4 |
| 10/5/2009 | 0.0 |
<tbody>
</tbody>
How do I ensure it calculates daily but then always rolls up into a dimDate[MonthEnd]?
Thanks,
Simon
PS: Cross posted to msdn forums
