Cumulative Sum/ Running Total | MDX

_{Friday, January 11, 2019 3:25 PM}

I have an SSAS Cube requirement (MDX) as below:

I am trying to create a calculated measure “Cumulative Sum/ Running Total” with the combination of multiple dimensions (around 7 dimensions). In addition, the users will be using any dimensions that they want. For example, we have a Product, Program, SubProgram, SubProgramStatus, Customer, and Date. The users should be able to add the dimension’s attribute to the lowest level as well as they should be able to roll-up the data to a higher level by excluding some of the dimensions such as the Product or SubProgramStatus, or both. Please note the users will be using the Report Layout as a Tabular Form and the can they should have the freedom to slice and dice.

I know how to create the cumulative measure by using the Date dimension such as YTD, but not sure how to create the MDX by including all the scenarios that the users might do, by including and excluding any dimension or attributes that they want. <g class="gr_ gr_25 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="25" id="25">Beside</g>, the users will have another non-cumulative measure that contains the base number for the cumulative measure, and the users will want to add the non-cumulative and cumulative side by side. By adding the cumulative and non-cumulative measures, the roll-up should show how the number increase. See the sample below:

Any suggestion would be appreciated

_{Friday, January 11, 2019 10:55 PM}

I think the standard cumulative patterns should just work. The reason for this is that the cumulative calculation patterns only override the context on the date dimension. They keep any existing filters from other dimensions in place.

So if you are doing one of the standard cumulative calculations like MTD, QTD, YTD or LifetimeToDate this should be easy. If you've tried this approach and it's not working for some reason can you post your code and an example of where it's producing the wrong result?

http://darren.gosbell.com - please mark correct answers

_{Saturday, January 12, 2019 5:28 PM}

Thanks, you are correct the MTD, QTD, YTD or LifetimeToDate are easy, but then the code needs to be dynamic to handle of the user used any level for the data hierarchy such as day, week, month or year and any combination with the other dimensions.  I will work on it more and ask for help if I still have problems.

_{Monday, January 14, 2019 1:53 AM}

... but then the code needs to be dynamic to handle of the user used any level for the data hierarchy such as day, week, month or year and any combination with the other dimensions...

Again, this should just work. It's one of the main advantages of working with a cube.

Getting the calculations to work across any level should just be a matter of making sure you have your date dimension configured correctly. Getting your attribute relationships "right" is the key here, you can look at articles like the following to get some guidance on doing that if you find that your calculations are not functioning as expected https://www.red-gate.com/simple-talk/sql/bi/creating-a-date-dimension-in-an-analysis-services-ssas-cube/

http://darren.gosbell.com - please mark correct answers

_{Monday, January 14, 2019 5:37 AM}

Hi Mik _2018,

I think you could refer to Darren's suggestions to use MTD to achieve goal. In addition, if possible, could you please describe "needs to be dynamic to handle of the user used any level for the data hierarchy such as day, week, month or year and any combination with the other dimensions" in details(your expecting  output)? And upload some sample for this.

Best Regards,
Zoe Zhi

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.