Consecutive months

[ligalbert]

I want to only show the customers ID of the customers who have done an order for 3 consecutive months. Is there a way I can create a measure for that 

 

[tamerj1]

Hi @ligalbert 
Here is a sample file with the solution https://we.tl/t-cA5KF3G3ii

RANK = 
RANKX ( 
    Orders,
    VAR CurrentDate = Orders[OrderDate]
    RETURN
        YEAR ( Orders[OrderDate] ) * 100 + MONTH ( Orders[OrderDate] ),,
        ASC,
        Dense
)

Filter Measure = 
VAR CurrentIDTable = CALCULATETABLE ( Orders, ALLEXCEPT ( Orders, Orders[ArCustomerID] ) )
RETURN
    SUMX ( 
        CurrentIDTable,
        VAR CurrentRank = Orders[Rank]
        VAR PreviousRanks = FILTER ( CurrentIDTable, Orders[RANK] >= CurrentRank )
        VAR Previous3Ranks = TOPN ( 3, CurrentIDTable, Orders[RANK], ASC )
        VAR Result = DIVIDE ( SUMX ( Previous3Ranks, Orders[RANK] ) - 3, 3 )
        RETURN
            IF (
                CurrentRank = Result,
                1
            )
    )

[amitchandak]

@ligalbert , Try a measure with help from date table

measure =
Var _1 = calculate(Countx(Values('Date'[MONTH Year]),CALCULATE(sum(Table[Order Count]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
return
countx(Values(Table[AV CustomerID]) ,calculate( If( _1 =3, [AV CustomerID], blank())))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.