Count Consecutive Months that a measure was positive or negative

DAX DAX Calculations
15

@nickjordan32,

This is a really interesting problem that I’ve been thinking about since you posted it initially, and think I finally cracked it. Had to throw a lot of DAX at it - here are the highlights:

  1. Used a series of table functions to create a physical table (pTable) summarizing the relevant fields and measures, where Total Over Under was not blank (but could be zero):
pTable = 

VAR vTable=
FILTER(
    ADDCOLUMNS(
        SUMMARIZE(
            Dates,
            Dates[MonthInCalendar],
            Dates[MonthnYear]
        ),
        "TotOU", [Total Over Under],
        "TotIndex", [Total Index]
    ),
    ISNUMBER( [TotOU] )
)

RETURN
vTable
  1. Created a row index calculated column in the table above:

Row Number =

    RANKX(
        pTable,
        pTable[MonthnYear],
        ,ASC
    )

  1. Added a calculated column with the previous row’s value of Total OU using TOPN(1) and EARLIER:

    Previous Value ToTOU =

    VAR PreviousRow =
TOPN(
    1,
    FILTER(
        pTable,
        pTable[MonthnYear] < EARLIER( pTable[MonthnYear] )
    ),
    [MonthnYear],
    DESC
)

VAR PreviousValue =
MINX(
    PreviousRow,
    pTable[TotOU]
)

RETURN
PreviousValue

  2. Created another calculated column using nested SWITCH( TRUE() ) functions to define a “win” (basically when Tot OU and previous Tot OU were the same sign, plus some other error trapping conditions):

    Is Win =

    SWITCH( TRUE(),
    pTable[Row Number] = MIN(pTable[Row Number] ),
        SWITCH( TRUE(),
            pTable[TotOU] = 0, 0,
            1
        ),
    pTable[TotOU] = 0, 0,
    pTable[Previous Value ToTOU] = 0,
        SWITCH( TRUE(),
            pTable[TotOU] > 0, 1,
            0
        ),
    SIGN( pTable[TotOU] ) = SIGN( pTable[Previous Value ToTOU] ),  1,
        0
)

  3. Finally, calculated the various win streaks based on the Is Win? calculated column above:

    Win Streak =

     VAR LastLossRowNum =
     CALCULATE(
         MAX( pTable[Row Number] ),
         FILTER(
             pTable,
             pTable[Is Win] = 0
         ),
         FILTER(
             pTable,
             pTable[Row Number] <= EARLIER( pTable[Row Number] )
         )
     )

 VAR Streak =
 pTable[Row Number] - LastLossRowNum

 RETURN
 Streak

Here’s what it looks like all put together:

image
image1128×254 80 KB

I hope this is helpful. Full solution file posted below.

P.S. Given that the entire solution was done via calculated columns, my sense is that this whole exercise is probably better done in Power Query, and I suspect @Melissa could do this in M with a fraction of the coding I did above in DAX. Also, shout out to Malagari on the Microsoft community forum for his conceptualization of the win streak logic behind the DAX calculation.

3 Likes