Hi Tm1 Users,
which
I have the below sales cube with following dimensions.
TImeperiod - Year -> Month->Date
Customer
Products
Sales Measures
Business Day ( Has days from 1 to 23 )
I am having a time period dimension which can track sales by calendar days , business requested to have a business day dimension which will have values from 1 to 23 .
if Apr -1 - Business day1
Apr - 2 - Business day 1
Apr - 3 - Business day 1
Apr -4 - Business day 2
If April 2 and April 3 are non business days their sales are still loaded to the prior business day .
so now they can see the sales by business day as well by picking a month on time period dimension .
Everything works so far for sales ,qty , cost etc...Sales on non business days goes to its prior business day .
Now they wanted to have a measure called RPD - Revenue Per day which is based on business day .
I dont know how to deal with this since I have a calendar day time period dimension .Also the calculation for RPD should be soemthing like
on business day 1 - Same as sales value
on business day 2 - Business day 1 sales + business day 2 sales / 2
now to perform this calculation on the fly and also load to only one calendar day when a business day can have more than 1 calendar day things are getting very complicated.
Any suggestions on how to proceed is highly appreciated.
Paid Advertisement:
