Forum Discussion

ste_ver's avatar
ste_ver
Frequent Visitor
8 years ago
Solved

Win / Losing Streak

hi,

i have a standard model (fact, dimcustomer, dimproduct, dimdate).

i have on my report a matrix with the total sales against the periods (column) and productcategory (rows).

What i now would like to do is make up the same matrix, but in stead of the total sales, i would like to display the win/lose streak.

I have made up an excel with what i would like to see.

Take f.i. productkey 4:

sales febr is lower than sales jan --> losing streak = -1 (minus sign as it indicates a loss)

sales march is highter than sales febr --> winning streak = 1 (plus sign as it indicates a win)

sales april is higher than sales march --> winning streak = 2

sales mey is higher ten sales april --> winning streak = 3

 

 

 

 

 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      ste_ver
      Frequent Visitor
      8 years ago

      thanks, but how clear your instructions may be, i can't seem to execute them.

      i'm already stuck in making up the summary table, my competence level of DAX is probably not as high as you might hope for.

      i know it is a lot to ask, but is it possible to sent me your pbix file, so that i can better follow the steps?

       

      thanks...