I’m not sure if this is possible or not but I’m trying to create a calculated member in our sales cube that will return the average working day sales (actuals). Where working days is sum of the Working Days Rate.
In our date dimension I’ve added a Working Day Rate member attribute that has a different weighting per day. Mon-Friday 1, Sat 0.5, Sun 0, Holiday 0.
Below is some sample data.
Long Date Day Name of Week Working Day Rate Actual MTD Average Working Day Actual
May 1, 2020 Friday 1.00 $3,155
May 2, 2020 Saturday 0.50 $9,849
May 3, 2020 Sunday 0.00 $632
May 4, 2020 Monday 1.00 $29,425
May 5, 2020 Tuesday 1.00 $34,336
May 6, 2020 Wednesday 1.00 $49,378
May 7, 2020 Thursday 1.00 $35,446
May 8, 2020 Friday 1.00 $44,828
May 9, 2020 Saturday 0.50 $9,142
May 10, 2020 Sunday 0.00 $653 $30,978
Sum of Working Days 7.00 $216,844
[Sample Data][1]
What I am trying to create is a calculated member which will sum up the Actuals and divide by the Sum of the Working Day Rates for a specified date. So in the above data sample the calculation would be: $216,844 / 7.00 = $30,978.
Where I’m stuck with is how to write the MDX to do the Sum of the Working Day Rate.
I’ve seen many examples where people count days in a date dimension like below, but I’m looking to sum the member attribute values not count them.
Count (
Descendants(
[Invoice Date].[Date - Hierarchy].Currentmember,
[Invoice Date].[Date - Hierarchy].[Day]
)
)
Is it possible to do a Sum on member attribute in date dimension and use it in another calculation (or the same one) to find the average sales by working days?
I’d also like to be able to do the calculation on any date level if possible Day, Month Year.
