topic Re: Count most recent and consecutive streak of values IF values are <1 in DAX Commands and Tips

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

Anonymous — Thu, 23 Feb 2023 09:29:41 GMT

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 🙂

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

FreemanZ — Thu, 23 Feb 2023 09:52:40 GMT

hi Anonymous

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:

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

Anonymous — Thu, 23 Feb 2023 10:22:05 GMT

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 🙂

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

FreemanZ — Thu, 23 Feb 2023 10:25:14 GMT

hi Anonymous

it may still work. Try and let us know.

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

Anonymous — Thu, 23 Feb 2023 10:31:55 GMT

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?

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

FreemanZ — Thu, 23 Feb 2023 11:15:32 GMT

hi Anonymous

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

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

Anonymous — Thu, 23 Feb 2023 12:22:24 GMT

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

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

FreemanZ — Thu, 23 Feb 2023 13:27:44 GMT

hi Anonymous

try like:

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

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

Anonymous — Thu, 23 Feb 2023 13:51:01 GMT

Hi FreemanZ,

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

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

Anonymous — Fri, 24 Feb 2023 15:42:09 GMT

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

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

JohnD61 — Sun, 02 Apr 2023 18:23:40 GMT

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