Categorize Values by Timeframe

[Anonymous]

Hi, community,

I have a matrix that looks like this:

ITEM STOCK
Week Number / Week Start	1	2	3	4	5	6	7
Item	1/01/2021	7/01/2021	14/01/2021	21/01/2021	28/01/2021	5/02/2021	12/02/2021
a		-2					-11
b	3	-4			5
c	6		5				-10
d	7		5			7	-7

I want to create a table/matrix that has an indicator for negative stocks over the next 6 weeks. Based on the table above, it should look like this.

Negative Stocks
Item	Negative in 6 weeks
a	Y
b	Y
d	N

Thanks

Evan

[Anonymous]

Hi @Anonymous 

As you said , negative is < 0 . Do you mean that the value of the seventh week is less than 0 ?If yes , then the result returned by d should also be Y. But the result you provided is N.

So your definition of negative is still not very clear , If possible, please give a detailed description of negative .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous ,

Y is only for negative in 6 weeks. Week 7 should not be included in this.

[Anonymous]

Hi @Anonymous 

What is the rule for you to judge negative? At present, from the data you provided, I have no way to judge under what circumstances it is negative and under what circumstances it is not? It would be great if you can provide specific judgment rules .

Best Regards

Community Support Team _ Ailsa Tao

[Anonymous]

Hi, @Anonymous ,

Negative is < 0.

Thanks

[amitchandak]

@Anonymous , Assume you have data in unpivoted format or you can unpivot it in power query having dates on row, and then can create a date table and a week rank on that

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Stock = if(sum(Table[Qty])<0,1,0) 

negative ove last 6 week = if( countx(values(Table[Weel]), calculate([Stock ],FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))) >0, "Yes", "No")

[Anonymous]

Hi @amitchandak,

Thanks for the reply.

What does Table[Weel]) refer to?

Thanks

Evan