Multiplier/Counter for consecutive months above 38% GM

[aclifton]

Hi everyone,

I'm trying to create a counter that will increase by 1 for each month when the GM% for the month is over 38%, which would then multiply another number by 1.5 after 3 consecutive months at 38%. If the 38% threshold is not met after this happens, the counter would reset to 0 and the multiplier would go back to 1 until the 3 month threshold is met again. I attached snippets of my formula and my results.

Currently, it is showing the value for every month with GM% over 38% as 4. Instead of this, I want it to start at 1 for the first occurance, then go to 2, and so on, and then go back to 0 and restart the cycle for when the GM% is not > 38%.  Any help would be greatly appreciated, please let me know if there is anything that needs to be cleared up! Thank you!

[v-jayw-msft]

Hi @aclifton ,

Please check the measures:

Measure = IF([Qualified Gross Margin %]>=0.38,[Qualified Gross Margin %],0)

Measure 2 =
var _1=RANKX(FILTER(ALL('Table'),[Measure]<>0),CALCULATE(MAX('Table'[Month Year Index])),,ASC,Dense)
return
IF([Measure]=0,0,_1)

[aclifton]

Hi Jay,

Thanks for the response. I tried your measures above and it still isn't correct.

['QGM If'] is your Measure

['Month Qualifier Test'] is your Measure 2

It seems that the number does appear as 0 when it is below 38%, but the count does not reset when an instance of a 0 happens. The count also seems to start at the first instance and just go up by 1 for each month. I need it to reset every time there is a 0. For example, in the beginning of the image where it starts at 1 , 2, 3, 4, 5, 6, 0... I need the Month Qualifier Test to return 1 again where it is returning 12, and again where it returns 18. This is because I need to apply a multiplier to any value greater than 4. Any additional help you could provide would be a huge help. Thanks again.

[aclifton]

Sure thing, heres a small table for all of 2021-2022. I need a measure that will count the number of months that have a Qualified Gross Margin % > 38%, and will them keep track of that moving into the next month and also reset if it does not meet that threshold. So for example, the measure would return 1 for Jan-21, 2 for Feb-21, and 3 for Mar-21, but then 0 again for Apr-21 because its not over 38% (but it would go to 4 if it was). Any help would be greatly appreciated. Thank you.

Please note in this table the Month Year and Month Year Index Columns are coming from a table called 'Calendar' and the Acct Manager Bonus and Qualified Gross Margin % are two measures that were created. 

Month Year	Month Year Index	Acct Manager Bonus	Qualified Gross Margin %
Jan-21	109	$0	50.91%
Feb-21	110	$0	41.49%
Mar-21	111	$0	38.11%
Apr-21	112	$0	33.28%
May-21	113	$0	36.17%
Jun-21	114	$0	28.55%
Jul-21	115	$0	36.35%
Aug-21	116	$0	30.55%
Sep-21	117	$3,500	38.18%
Oct-21	118	$3,000	37.68%
Nov-21	119	$0	31.28%
Dec-21	120	$0	31.41%
Jan-22	121	$0	37.32%
Feb-22	122	$2,500	36.11%
Mar-22	123	$4,500	42.04%
Apr-22	124	$4,500	42.04%
May-22	125	$4,500	41.78%
Jun-22	126	$4,000	39.84%
Jul-22	127	$4,500	40.99%
Aug-22	128	$4,500	44.09%
Sep-22	129	$4,500	44.55%
Oct-22	130	$4,500	45.95%
Nov-22	131	$4,500	47.17%

[v-jayw-msft]

Hi @aclifton ,

Please share some sample data in text format so that we could use it test the formula.

Best Regards,

Jay