Count If Measure For Last 3 Months Above Value

[AngelaMarie]

I have a table of data that is in the format below:

Category	Month	Value 1	Value 2	% Value
A	Aug 2020	10	100	10%
A	Sep 2020	25	100	25%
A	Oct 2020	20	100	20%
A	Nov 2020	30	100	30%
B	Aug 2020	30	100	30%
B	Sep 2020	25	100	25%
B	Oct 2020	20	100	20%
B	Nov 2020	5	100	5%

I need to calculate how many categories have  achieved a % value >= 20% for the last 3 consecutive months rolling. So in the example above for Nov the count should be 1 (category A) as the % value was >= 20% for the last 3 months rolling. 
I'm not sure what the best way is to achieve this? I was thinking if I ended up with something like below than I can just sum the 3rd column for each of the months so for Oct there is 1 category that ended up achieving >= 20% for previous 3 months and for November there is also 1 category that ended up achieving >=20% for previous 3 months. 

Category	Month	>= 20% for last 3 months
A	Oct 2020	0
A	Nov 2020	1
B	Oct 2020	1
B	Nov 2020	0

But if anyone has any better ideas? The Month value is from a date calendar, so it not initially grouped by month. I need the % value grouped by month before summing them over the 3 months rolling. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AngelaMarie]

Redoing the tables as they are a bit difficult to see in the last post

This is the first table

Category__	__Month__	__Value 1__	__Value 2__	__% Value__
A	Aug 2020	10	100	10%
A	Sep 2020	25	100	25%
A	Oct 2020	20	100	20%
A	Nov 2020	30	100	30%
B	Aug 2020	30	100	30%
B	Sep 2020	25	100	25%
B	Oct 2020	20	100	20%
B	Nov 2020	5	100	5%

The Second table:

__Category__	__Month__	__>= 20% last 3 months__
A	Oct 2020	0
A	Nov 2020	1
B	Oct 2020	1
B	Nov 2020	0

[V-pazhen-msft]

@AngelaMarie 

Can you also post a expect outcome based on the sample you provided, I will go for a test.

Regards
Paul

[AngelaMarie]

I would like to be able to show the count of categories >20% for the 3 month rollingd. Something like below

Month	Jan 2020	Feb 2020	Mar 2020
Category Count > 20%	2	4	3

So in the above example under January 2020, there were 2 categories that achieved >20% for Nov 2019, Dec, 2019 and Jan 2020

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AngelaMarie]

That's perfect. Exactly what I was after. Thanks so much!! 🙂

[AngelaMarie]

I'm having a problem with the data. I changed the % value so it was a measure instead called "New % Value" in the image below:

The New % Value is calculated as follows: 

New % Value = DIVIDE(SUM(Data[__Value 1__]),SUM(Data[__Value 2__]))

I then added some more values for 1st of July so the totals are as above. I modified the existing ">20%" measure to take account of the new % value measure and its called "New >= 20% last 3 months" below

So as you can see for Category A its flagging September as having greater than 20% for the last 3 months but only two of the months were greater than 20%.

The modified measure is

[New >= 20% last 3 months__] = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Category__]),Data[Category__],"ABCD",countrows(FILTER(CALCULATETABLE('Calendar',DATESBETWEEN('Calendar'[Date],edate(MIN('Calendar'[Date]),-2),max('Calendar'[Date]))),[New % Value]>=0.2))),[ABCD]=3)).

Any idea why this is not working?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[AngelaMarie]

Here is the link to the file

[Ashish_Mathur]

Hi,

For July 2020, there are multiple entries of A.  So should the denominator of A for July 2020 be 500 or 100?

[AngelaMarie]

500

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AngelaMarie]

Thank You!

[Ashish_Mathur]

You are welcome.

[AngelaMarie]

Hi please find link to report. 

I haven't added any formula's - just added another category and some more values. Unfortunately, the greater than 20% measure is not calculating correctly. See category C in the workbook for Jul, Aug, Sep.

[Ashish_Mathur]

Hi,

I am unable to solve it.  May be someone else can help you.

[AngelaMarie]

Thanks for giving it a go. 

[Ashish_Mathur]

You are welcome.