0

this is my dataset:

enter image description here

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

CodeNotFound
  • 22,153
  • 10
  • 68
  • 69
TimB83
  • 1

1 Answers1

0

If you just require a 1 or 0 then can things not be simplified:

IIF(
  SUM(
    {[TIME].[Year to Month].currentmember : NULL},
     [Measures].[cum Sales]
  ) 
  < ([Measures].[Stock],[TIME].[Year to Month].&[Jan-2016]) //<<amend to the date format used in your cube
,1
,NULL
)
whytheq
  • 34,466
  • 65
  • 172
  • 267