February 14, 2016 at 3:22 pm
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
February 14, 2016 at 4:20 pm
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.
February 14, 2016 at 4:36 pm
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