Win / Losing Streak

ste_ver,

It seems that  you post wrong winning streak value for productkey 3 as sales may is lower than sales april.

Firstly, you should create a new summary table as shown in the following table.


Secondly, create the following columns in the summary  table.

PreviousRow = 
var previous=CALCULATE(FIRSTNONBLANK(Table1[Column1],Table1[Column1]),FILTER(Table1,Table1[ProductCategoryKey]=EARLIER(Table1[ProductCategoryKey])&& Table1[Index]=EARLIER(Table1[Index])-1))
return
IF(ISBLANK(previous),BLANK(), Table1[Column1]-previous)

checkwinorloss = IF(ISBLANK(Table1[PreviousRow]),0,IF(Table1[PreviousRow]>0,1,-1))

Previouscheck = 
var previous=CALCULATE(FIRSTNONBLANK(Table1[checkwinorloss],Table1[checkwinorloss]),FILTER(Table1,Table1[ProductCategoryKey]=EARLIER(Table1[ProductCategoryKey])&& Table1[Index]=EARLIER(Table1[Index])-1))
return
IF(ISBLANK(previous),BLANK(), previous)

Column = CALCULATE(FIRSTNONBLANK(Table1[Index],Table1[Index]),FILTER(Table1,Table1[ProductCategoryKey]=EARLIER(Table1[ProductCategoryKey]) && Table1[checkwinorloss]=-1&& Table1[Index]<>MAX(Table1[Index])))

lastColumn = IF( Table1[Column]=BLANK(),1, Table1[Column])

Winning steak = 
IF(Table1[checkwinorloss]=-1||Table1[checkwinorloss]=0||Table1[Previouscheck]=-1,Table1[checkwinorloss],Table1[Index]-Table1[lastColumn])

Regards,
Lydia

ste_ver,

Create a new table using dax below, then create Win/Losing streak following the instrutions in my first reply, the MonthNumberOfYear column acts as the Index column in my sample table. If you have any questions, feel free to ask.

Table = SUMMARIZE(FactInternetSales,DimProductCategory[EnglishProductCategoryName],DimDate[MonthNumberOfYear],DimDate[EnglishMonthName],"Sales",[Sales])

Regards,
Lydia

thanks. works like a charm!