Count the consecutive instances when a certain criteria is met using power query editor

[Anonymous]

I want to include a custom column that counts how many weekendings that products have resulted in poor performance starting with the most recent weekending period: 12.22.2019. If the poor performance didn't occur on W.E. 12.22.2019 then the result would be 0.

Below is a small sample of a much larger set of data where the result would be the "Count Poor Performance" column

Product	Performance	Weekending	Count Poor Performance
XYZ	Poor	12.15.2019	2
XYZ	Poor	12.22.2019	2
ABC	Good	12.08.2019	0
ABC	Good	12.15.2019	0
CDF	Poor	12.22.2019	1

[Jimmy801]

Hello @Anonymous ,

If you would like to use Power Query for this, you can use this query.

It does a grouping and filtering on "poor" and using this result to join the initial table. Here the code to check if this could fit

let
	Source = #table
	(
		{"Product","Performance","Weekending"},
		{
			{"XYZ","Poor","43814"},	{"XYZ","Poor","43821"},	{"ABC","Good","43807"},	{"ABC","Good","43814"},	{"CDF","Poor","43821"}
		}
	),
    Date = Table.TransformColumns
	(
		Source,
		{{"Weekending", each Date.From(Number.From(_)), type date}}
	),
    Group = Table.Group
	(
		Date, 
		{"Product", "Performance"}, 
		{{"CountRows", each Table.RowCount(_), type number}}
	),
    FilterForPoor = Table.SelectRows
	(
		Group, 
		each ([Performance] = "Poor")
	),
	Join = Table.NestedJoin
	(
		Date,
		{"Product", "Performance"}, 
		FilterForPoor, 
		{"Product", "Performance"}, 
		"CountPoor"
	),
    Expand = Table.ExpandTableColumn
	(
		Join, 
		"CountPoor", 
		{"CountRows"}, 
		{"CountRows"}
	)
in
	Expand

Copy paste this code to the advanced editor to see how the solution works. You can afterwards apply this steps to your original query by copy pasting a part of it. Otherwise I could create a function out of it and you can implement it with the custom function in a easier way.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801,

Thank you for looking into this so far.

One element I want to make sure is fulfilled is the count function only applies when the current most weekending (max weekending) is poor performance.

Consider that a trigger to start counting the consecutive instances of previous weeks.

See the clarified dataset below with a more clear result in mind. The most recent weekending being: 12/29/2019

Weekendings	Product	Performance	Result (Consecutive WEs)
12/15/2019	ABC	Poor	3
12/22/2019	ABC	Poor	3
12/29/2019	ABC	Poor	3
12/15/2019	XYZ	Poor	1
12/29/2019	XYZ	Poor	1 because not consecutive (12/22/2019) wasn't active
12/15/2019	FGH	Poor	0
12/22/2019	FGH	Poor	0 because 12/29/2019 wasn't active
12/22/2019	BNM	Poor
12/29/2019	BNM	Good	0 because 12/29/2019 is Good performance

[Jimmy801]

Hello @Anonymous 

this deviates a lot from what you have asked in the first post. Remember to post such important things always in the beginning.

Jimmy

[edhans]

The following measure will do this I believe. I'd have to tinker with it a bit to get a calculated column to work, but if the goal is to report a table/visual with the info you want, a measure is a better bet.

Count of Poor Performance = 
VAR LatestWeekEnding = MAX('Product Performance'[Week Ending])
VAR PoorPerformancecount =
        CALCULATE(
            COUNTA('Product Performance'[Product]),
            FILTER(
                ALLEXCEPT('Product Performance','Product Performance'[Product]),
                MAX('Product Performance'[Performance]) = "Poor"
            )
        )
VAR PoorPerformanceList = 
    CALCULATETABLE(
        DISTINCT('Product Performance'[Product]),
        'Product Performance'[Performance] = "Poor",
        'Product Performance'[Week Ending] = LatestWeekEnding
    )
RETURN
IF(MAX('Product Performance'[Product]) in PoorPerformanceList,PoorPerformancecount,BLANK())

I get a table that looks like this: