topic 3 in a row - consecutive monthly total greater than n value in DAX Commands and Tips

3 in a row - consecutive monthly total greater than n value

MrMani — Tue, 26 Mar 2024 02:51:40 GMT

Need a help on this measure to show each ID - 3 in a row - consecutive monthly total greater than <n> value

find the below example data (summarized table with 3 columns)

Ex: n=15

for ID =1, 3 consecutive points aceived on Sep 23 (Consecutive months acheived on Jul, Aug,Sep 23)
for ID =2, 3 consecutive points aceived on May 23 (Consecutive months acheived on Mar, Apr, May 23)
Points is a monthly total points - measure; Calendar/Date table is mapped with this summary table
every ID's starting month value varies

ID	Month	Points	3 in a row
1	Feb-23	15
1	Mar-23	8
1	Apr-23	16
1	May-23	14
1	Jun-23	2
1	Jul-23	18
1	Aug-23	16
1	Sep-23	20	Y
1	Oct-23	23
1	Nov-23	17
1	Dec-23	8
1	Jan-24	6
2	Aug-22	12
2	Sep-22	34
2	Oct-22	4
2	Nov-22	45
2	Dec-22	2
2	Jan-23	56
2	Feb-23	5
2	Mar-23	20
2	Apr-23	23
2	May-23	17	Y
2	Jun-23	28
2	Jul-23	6

Re: 3 in a row - consecutive monthly total greater than n value

vicky_ — Tue, 26 Mar 2024 03:51:08 GMT

Try something like the below:

3 in a row = var prev = SUMX(WINDOW(0, REL, 2, REL, ADDCOLUMNS(ALLSELECTED('Table'[ID], 'Table'[Month], 'Table'[Points]), "> n value", INT('Table'[Points] > 15)), ORDERBY('Table'[Month], DESC), PARTITIONBY('Table'[ID])), [> n value] ) RETURN IF(prev >= 3, "Y")

I'm pretty sure you can use a numeric slider to replace the N value = 15 if you need to make it dynamic.

And check out the docs for the window function: https://learn.microsoft.com/en-us/dax/window-function-dax

Re: 3 in a row - consecutive monthly total greater than n value

MrMani — Tue, 26 Mar 2024 14:18:51 GMT

Thanks much Vicky.
Tried multiple flags with iterative functions, had performance hit.
But, this window function approach is upfront, and works like a charm:)