Forum Discussion
Categorize Values by Timeframe
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/...
amitchandak
Super User
Super UserAnonymous , 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")