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

  • ste_ver's avatar
    ste_ver
    8 years ago

    thanks. works like a charm!

ste_ver's avatar
ste_ver
Frequent Visitor
8 years ago

thanks for the file, it indeed seems to work.

however, you already start from a table which i am having trouble setting up.

my facttable is not set up like your table, my fact table consists of thousands of rows with sales per day for customers and products.

my table is not made up as the sum of a key with a calculated index... Lydia suggested setting up a new table, but i don't have a clue how to do it.  after some googling, i came up with

New table: Table1 = SUMMARIZE( 'Fact' ; Articles[ProductGroupKey] ;'Fact'[Date] ;"Sales" ; sum('Fact'[NettSlesIndex]) )

But, that didn't work. the calculates sales was about 50% higher than it was in my fact table, and the context filter didn't have any impact on the calculatoin...

So, however much appreciated, i'm not any wiser...

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    ste_ver,

    Please share complete data of your table for us to analysis.

    Regards,
    Lydia