MDX: Calculating MONTH COVER (of Stock) in a performant way

  • Hello,

    this is my dataset:

    http://www.sqlservercentral.com/Forums/Attachment18473.aspx

    I want to calculate the "Cover Month". Therefore I have to look for Stock(in this example in january 2016 = 5,000), then have a look for each future month if current stock(january 2016) is bigger than "cum. Sales" of following month. If yes, then remember value = 1. This should be done for each future month. After this step all remembered values should be added, so result is 4 (Cover Month). Stock will be enough for 4 following months.

    Next step system should do this for next month - dynamically for each month...

    How can I do this in a performant way?

    Is this the right way:

    Filter([TIME].[Year to Month].currentmember : NULL,

    [Measures].[cum Sales] < [Measures].[Stock]

    )

    ?

    Maybe anybody can give me a hint? Or maybe I need another alternative formula to get a subtotal and then do another calculation?

    Thanks in advance, Andy

  • For this, you'd be better to work this out in the ETL and create a snapshot fact table. Over time, you'll always have to cover all the historical sales data in each query.

  • Hello,

    yes I will check this if it is possible to do it by Stored Procedure or anything else, but my goal is to get a solution with MdX, I think there must be a way, becuse user can only select maximum of 6 months in overview, so a calculation must be possible.

    Do you have any idea how I can do this?

    Kind regards,

    Andy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply