Forum Discussion

ligalbert's avatar
ligalbert
Helper I
4 years ago

Consecutive months

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   
Need Help
Show and Tell
tamerj1's avatar
tamerj1
Community Champion
4 years ago

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