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
  • Anonymous's avatar
    Anonymous
    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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

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...