MDX calculation of Daily Sales Outstanding

• Question

• Hi,

I have a financial cube and i have to calculate Daily Sales Outstanding as :

Number of Days between the selected month last date and the earliest transaction date when cummulative sum of Revenue from last date of the month till the date where sum revenue <= the debt amount for the date .

e.g

On 31/12/2009 my debt amount = 2,500,000

31-Dec-09     30-Nov-09    15-Oct-09     31-Oct-09

Revenue                                   1,000,000     1,000,000    500,000          1,0000

Cummulative sum of revenue     1,000,000     2,00,000     2,500,000      4,000,000

No of Days                                    31            30                 16

On 15/Oct/09 cummulative revenue is 2,500,000 which equals my debt amount on that day

Count of Days = 31 + 31 + 16 = 76 Days.

In other words Sum Revenue from the selected date backwards until sum total equals or exeeds the total to date balance of the debtors.

Any help will be highly appreciated .

If i haven't explained clearly enough or if you need more information then please let me know.

Shuchi.

shuchi
Friday, November 26, 2010 10:36 AM

• ```WITH MEMBER Revenue AS
[Measures].[Internet Sales Amount]
MEMBER CumulativeRevenue AS
SUM( NULL : [Date].[Calendar].CURRENTMEMBER,
[Measures].[Internet Sales Amount])
MEMBER NoOfDays AS
Count(nonempty
(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
[Measures].[Internet Sales Amount]))
MEMBER CumCountOfDays AS
SUM( NULL : [Date].[Calendar].CURRENTMEMBER,Count(nonempty
(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
[Measures].[Internet Sales Amount])))

SET Months AS
{FILTER([Date].[Calendar].[Month],CumulativeRevenue<=Revenue).Item(0) :
[Date].[Calendar].[Month].&[2002]&[7]
}
SELECT
{Revenue,CumulativeRevenue,NoOfDays,CumCountOfDays} on 0,
Months on 1
FROM
```

Not sure how far i have reached here to address your problem...I have taken AW example.

Internet sales amount is like the revenue.

In AW, all are values are positive values. I guess in your case there may be negative values for revenue. As a result of all the values being positive it will fetch the data from 1st month in the hierarchy for my example as this is the only date when SUM of cumulative revenue<=revenue. It am doing item(0) because there may be several months where it is <=revenue and i get the first instance. you might have to tweak here if u want the last occurring month.

Noofdays: Fetches count of days for which there is internet sales in the fact table. Hence a nonempty is there.

CumCountOfDays: fetches you Cumulative count of days

Let me know

Note: {I might have misunderstood: Number of Days between the selected month last date and the earliest transaction date when cummulative sum of Revenue from last date of the month till the date where sum revenue <= the debt amount for the date .}

vinu
• Proposed as answer by Monday, November 29, 2010 7:08 AM
• Marked as answer by Monday, November 29, 2010 9:42 AM
Friday, November 26, 2010 2:16 PM

All replies

• ```WITH MEMBER Revenue AS
[Measures].[Internet Sales Amount]
MEMBER CumulativeRevenue AS
SUM( NULL : [Date].[Calendar].CURRENTMEMBER,
[Measures].[Internet Sales Amount])
MEMBER NoOfDays AS
Count(nonempty
(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
[Measures].[Internet Sales Amount]))
MEMBER CumCountOfDays AS
SUM( NULL : [Date].[Calendar].CURRENTMEMBER,Count(nonempty
(Descendants([Date].[Calendar], [Date].[Calendar].[Date]),
[Measures].[Internet Sales Amount])))

SET Months AS
{FILTER([Date].[Calendar].[Month],CumulativeRevenue<=Revenue).Item(0) :
[Date].[Calendar].[Month].&[2002]&[7]
}
SELECT
{Revenue,CumulativeRevenue,NoOfDays,CumCountOfDays} on 0,
Months on 1
FROM
```

Not sure how far i have reached here to address your problem...I have taken AW example.

Internet sales amount is like the revenue.

In AW, all are values are positive values. I guess in your case there may be negative values for revenue. As a result of all the values being positive it will fetch the data from 1st month in the hierarchy for my example as this is the only date when SUM of cumulative revenue<=revenue. It am doing item(0) because there may be several months where it is <=revenue and i get the first instance. you might have to tweak here if u want the last occurring month.

Noofdays: Fetches count of days for which there is internet sales in the fact table. Hence a nonempty is there.

CumCountOfDays: fetches you Cumulative count of days

Let me know

Note: {I might have misunderstood: Number of Days between the selected month last date and the earliest transaction date when cummulative sum of Revenue from last date of the month till the date where sum revenue <= the debt amount for the date .}

vinu
• Proposed as answer by Monday, November 29, 2010 7:08 AM
• Marked as answer by Monday, November 29, 2010 9:42 AM
Friday, November 26, 2010 2:16 PM
• Thanks a lot.

I applied the approach but the query is taking more than 10 min to return results This is becoz we have a huge date dimension. I will have to modify to use it on a month level only.

Thanks again to help me think into a way to resolve this.

Shuchi.

shuchi
Monday, November 29, 2010 9:42 AM