Categorize Values by Timeframe

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")

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

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.