Count Consecutive Times a Calc is above 0

Hi Danilo

 

You don't write how the data is presented, so assumed it was with month on the X axis, and shops on Y axis. Otherwise if you data is different, and this doesn't work, or you can't make it work just say the word :) Also in my example I count months < 4000 turnover, and then if months are consecutive. 

Furthermore my date criteria is the last 13 months, and then I hide the first month. This is so I can see if the first month of the last 12 months, was also consecutive. In my case I get 5 consecutive months.

I made a new measure that have a value = 1 if last month was less than 4000 and the next month is as well. 

if allcount(d1:0, 0, m1) = 1 then 0 else if PreviousMonth:(sum(-1, 0, m1)) 
< 4000 and CurrentMonth:(sum(0, 0, m1)) < 4000 then 1 else 0

 

The 

if allcount(d1:0, 0, m1) = 1 then 0

is just to make sure that the hidden month doesn't count as consecutive, don't think it will but I then I am sure :)  

And then you just sum that measure in a new calculated column. 

sum(all, 0, m2)

 

Hi Kristopher, thanks!

This solution almost works. The only thing is that I'd like to stop counting when they stop being consecutive.

For instance, in my example below, I'd like to count only the months since May-2021 that were consecutively > 40mil, but it counts the ones past that as well.

That's being my struggle with it. I know there must be a way! haha

Thanks again,

Danilo

Hi Danilo, 

 

So your result in your example should be 3 instead of 4, because you don't want to count the last available month, is that correct?

Hi Kristoffer,

In my example I'd count May-2021, Apr-2021 and Mar-2021. The past months would be ignored because Feb-2021 was <40mil.

Hi Danilo,

Look at this example:

x: if sum(0, 0, m1) < 25 then 1 else 0
y: if sum(0, 0, m2) = 1 then sum(-1, 0, m3, 0) + 1 else 0
Consecutive last months less than 25: sum(d-1, 0, m3)

Hi Ole, I will test this out and let you know, but it seems it will work!

Thanks!