Count most recent and consecutive streak of values IF values are <1

[FVP]

Hi everyone,

I'm trying to make a measure that: counts how many times a Product has a Value below 1,0. The count has to take into account that it is consecutive, but also counts down from the most recent week.

In my example below:
- Product A should get a measure count of 0 (because the most recent week, W3, has a value of above 1,0)
- Product B should get a measure count of 2 (because the most recent two weeks are below 1,0).

Product	Week	Value
A	W1	0,9
A	W2	0,9
A	W3	1,1
B	W1	1,0
B	W2	0,9
B	W3	0,9

I hope someone has an idea on how to make this work. I could not find anything that tackled the 'most recent week' part of the question unfortunately.

Thanks in advance for taking the time and effort 🙂

[FreemanZ]

hi @FVP 

try to

1) add a calculated column with:

Week2 = SUBSTITUTE([Week],"W", "")

(It is better if you can do it in Power Query Editor)

2) write a measure like:

Count = 
VAR _maxweek = MAX(TableName[Week2])
VAR _week = 
MAXX(
    FILTER(
        TableName,
        TableName[Value]>=1
    ),
    TableName[Week2]
)
RETURN
_maxweek - _week

it worked like:

[FVP]

Hi FreemanZ,

Your solution is working, but my dataset has made it a bit more complicated. 
I also have Products that dont have any values, so they are blank. This messes up the count measure you have written. The measure now also counts the blanks.

What would I need to alter in the measure to keep it from counting blanks as <1,0?

Thanks for your help! Happy with the current result either way 🙂

[FreemanZ]

hi @FVP 

it may still work. Try and let us know.

[FVP]

Hi FreemanZ,

Unfortunately, the ones that have only blanks are the one with get a count of 52 (52weeks).
It works great for the other ones though, as you can see from my screenshot below.

So I need something like a COUNTA that ignore blank, but Im not sure how I would fix this in this measure. Would you know this perhaps?

[FreemanZ]

hi @FVP 

could you expand your sample data and let us have a try?

[FVP]

Hi FreemanZ,

Thanks for taking the time to help me.

You measure works good for capturing 0 for Product A and 2 for Product B.

Two new examples added:
Product C in the example below would get a 3, but should get a 0 instead (edit: or maybe preferably, blank. But 0 is fine too).
Product D works good. It records a 0

Product	Week	Value
A	W1	0,9
A	W2	0,9
A	W3	1,1
B	W1	1,0
B	W2	0,9
B	W3	0,9
C	W1
C	W2
C	W3
D	W1	0,9
D	W2
D	W3	1,1

[FreemanZ · Accepted solution]

hi @FVP 

try like:

Count = 
VAR _maxweek = MAX(TableName[Week2])
VAR _week = 
MAXX(
    FILTER(
        TableName,
        TableName[Value]>=1||TableName[Value]=BLANK()
    ),
    TableName[Week2]
)
RETURN
_maxweek - _week

[JohnD61]

Hi @FreemanZ - I need to do something similar and was hoping to use your solution. .. but my count comes up as zero's. Any ideas would be appreciated. Thanks, John

[FVP]

Hi,

I know I've accepted the solution, but I was hoping you might know a quick fix for the following.

Whenever I add a slicer, to filter on only a specific number of weeks, measure cant cope with it.

When I set a slicer for only W2 and W3, for example.

Is there a quick fix in the measure for this? I've tried multiple things but couldnt work it out im afraid. It's driving me nuts!

Product	Week	Value
A	W1	0,9
A	W2	0,9
A	W3	1,1
B	W1	1,0
B	W2	0,9
B	W3	0,9
C	W1
C	W2
C	W3
D	W1	0,9
D	W2
D	W3	1,1

[FVP]

Hi FreemanZ,

That did the trick! It returns the right value in all my testrows 🙂
Thank you for your patience and time!