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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy