Hi
I couldn't tell what varies month by month so I have assumed that the number of units is the number of units in use and that this and the Building's Monthly Admin Fee per Unit that can vary over time.
Firstly, don't have separate year and month dimensions. Just have a single time dimension with months, eg 2018-01, 2018-02 etc. Add consolidations for Year, you can potentially consolidate different groups of months for Calendar Year, Financial Year, Tax Year, etc. Construct the dimension so that all base level months are listed first, before any consolidations. List the months in order. The old fashioned Dimension Worksheet approach can be the best way to do this for a beginner. You can use Excel Formula to generate the months and consolidations and auto fill down. There are other ways. I use an Excel VBA Macro sheet that I can parameterise for different start and end years, etc, others use TI and SQL.
I suggest you have a Building_Revenue cube with dimensions :
Effective_Mth (the months dimension described above)
Building (one element for each building with an All Buildings total consolidation)
The measures dimension Building_Revenue_Meas will have elements
Number of Units
Building Monthly Admin Fee per Unit
Monthly Revenue
(Obviously you might want to add other dimensions, such as Version, etc, but the above will do for the immediate problem)
I suggest that you create a Building_Info cube for visibility although you could just create attributes on the Building dimension.
This will have dimensions of
Building
and a measures dimension Building_Info_Meas with one String (Text) element
Billing Start Month
Then, the rule in the Building_Revenue cube will be
Code: Select all
SKIPCHECK ;
['Monthly Revenue'] = N:
['Number of Units']
*
['Building Monthly Admin Fee per Unit']
*
IF( dimix( 'Effective_Mth' , !Effective_Mth )
<
dimix( 'Effective_Mth' , DB( 'Building_Info' , !Building , 'Billing Start Month' ) )
,
0
,
IF( dimix( 'Effective_Mth' , !Effective_Mth )
=
dimix( 'Effective_Mth' , DB( 'Building_Info' , !Building , 'Billing Start Month' ) )
,
1/3
,
IF( dimix( 'Effective_Mth' , !Effective_Mth )
=
dimix( 'Effective_Mth' , DB( 'Building_Info' , !Building , 'Billing Start Month' ) ) + 1
,
2/3
,
1
)
)
) ;
FEEDERS ;
['Number of Units'] => ['Monthly Revenue'] ;
The first check on the Effective Mth being before the Billing Start Month is just to ensure that nothing will be charged even if someone accidentally puts in a Number of Units and Admin Fee before the Building is opened.
There might be the odd syntax error in the above as I haven't actually built this, but it should give you a starting point.
Regards
Paul Simon