Sum Amount Before Specific Date Across The Month Dimension

[#1]

Hi,

Basically I have a fact and a date dimension tables as shown here:

FactTable:

|FactTransDate | Amount |
|------------------|
|01/04/2012 | 256 |
|02/12/2012 | 2056 |
|03/07/2012 | 112 |
|04/09/2012 | 95 |
|05/05/2012 | 1069 |
|07/13/2012 | 125 |
|08/17/2012 | 73 |

|09/21/2012 | 73 |

|10/16/2012 | 73 |

|11/17/2012 | 73 |

|12/25/2012 | 73 |

|01/04/2013 | 456 |
|02/12/2013 | 4056 |
|03/07/2013 | 412 |
|04/09/2013 | 45 |
|05/05/2013 | 4069 |
|07/13/2013 | 425 |
|08/17/2013 | 43 |

|09/21/2013 | 473 |

|10/16/2013 | 373 |

|11/17/2013 | 273 |

|12/25/2013 | 573 |

|01/04/2014 | 3456 |
|02/12/2014 | 44056 |
|03/07/2014 | 2412 |
|04/09/2014 | 345 |
|05/05/2014 | 24069 |
|07/13/2014 | 4425 |
|08/17/2014 | 243 |

|09/21/2014 | 3473 |

|10/16/2014 | 4373 |

|11/17/2014 | 3273 |

|12/25/2014 | 6573 |

And the Date Dimension table has simply these fields: Year, Month, Date.

What I need to do is when I select a year from the calendar is to sum the ACCUMULATED amount for the beginning and ending for each month (my dimension is the month on a Straight Table Chart) within that year. So my Output should be something like this when I select 2014 for example:

--------------------------------------------------------------------------------------------------------

DateDimMonth |                    BeginningAmount              |     EndingAmount

Jan          |     XXXX (total everything before Jan 2014)     | BeginningAmount + All Trans Amount                                                                                     of current month

Feb          |     Jan 2014 Ending Amount                      | Beginning + Current Amount

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

--------------------------------------------------------------------------------------------------------

I tried this definition for the BeginningAmount:

sum({1<FactTransDate= {"<$(=min([TransactionDateDimCalendar.Date]))"}>} [Amount] )

And this for EndingAmount:

sum({1<FactTransDate= {"<$(=max([TransactionDateDimCalendar.Date]))"}>} [Amount] )

So both of them work fine if I don't add the month calendar dimension to the straight table chart. But when I add the monthly dimension, it's calculating  the amount for the previous period by grouping the amount on a monthly basis (i.e. All the amounts for Jan across the previous years).

So, my question is: How can sum and aggregate my data and lay it off like the table mentioned above?

Thanks!

[sujeetsingh]

First of all you have a complete set of to-do task so do not expect to get the complete solution in this thread.

I will recommend you to first follow the community with Accumulation and all facts related to it.

I think in spite of adding  a lot of conditions in calculation you should opt for the script.

Now for beiginnng amount use the logic that Datte >= selected date.