Checking if value is in same month last year

[nbs333]

Hi, I am trying to check if a Customer Id in the most recent month shows up in the same month last year. I am creating a flag that will segment the customer into 3 catagories.

IsNew = In current month but not in same month last year

IsLost = Not in current month but in same month last year

IsRetained = in both time periods

I can create two lists, one for IsNew and one for IsLost by using EXCEPT to show Customer Ids that should be in each catagory.

The problem I am having is checking if a specific Customer ID, say an Id in a row context, is in either of these lists. 

Here is the DAX I am usings and some sample data. Any help is greatly appreciated.

CustomerFlag = 
VAR mostrecentmonth = LASTDATE(BoB[ReportingDate])
VAR prioryear = SAMEPERIODLASTYEAR(mostrecentmonth)
VAR new = CALCULATETABLE( VALUES(BoB[CustomerKey]), Dates[Date] = mostrecentmonth)
VAR old = CALCULATETABLE( VALUES(BoB[CustomerKey]), Dates[Date] = prioryear)
VAR IsNew = EXCEPT(new,old)
VAR IsLost = EXCEPT(old,new)
VAR selectedrow = SELECTEDVALUE(BoB[CustomerKey])

VAR lostfiltered = FILTER(IsLost, selectedrow  IN IsLost)
VAR newfiltered = FILTER(IsNew, selectedrow IN IsNew)

VAR flag =
    SWITCH(TRUE(),
        COUNTROWS(IsNew) > COUNTROWS(newfiltered), "IsNew",
        COUNTROWS(IsLost) > COUNTROWS(lostfiltered) , "IsLost",
        "IsRetained"
    )
RETURN
flag

[Whitewater100]

Hi:

This could potentially help compare these lists by month. 

First start with adding calc col to your Date Table to keep track of months:(it will assign 1 to your first month all the way uo to current month.

Running Month Index =

VAR minyear = YEAR(MIN(Dates[Date]))

VAR thisyear = YEAR(Dates[Date])

Return

(thisyear - minyear) * 12 + MONTH(Dates[Date])

Measure:

Cust Date of First Purchase =

CALCULATE(MIN(

Sales[Purchase Date]),

REMOVEFILTERS(Dates)

)

Then you can make tables to compare. Here is an example of a table going back a year.

Cust in month (-12 months) =

var fisrtpurchase = [Cust Date of First Purchase]

return

CALCULATETABLE(

ADDCOLUMNS(

VALUES(Sales[Customer ID]),

"FirstPur", [Cust Date of First Purchase]),

Dates[Running Month Index] = MAX(Dates[Running Month Index]) -12)

This will be dynamic and update as time goes by.

This should match your Customer Count by ID from a year ago.(I tested with DistinctCount:

No. Customers = DISTINCTCOUNT(Sales[Customer ID])

I'm not sure if this exactly what you want but you can compare using IN command, EXCEPT.

I hope this helps you get a step closer to what you need.

[nbs333]

Thank for the help @Greg_Deckler. I can't seem to get it working as a measure; it returns all values as "IsRetained". I'm thinking it might work better as a calculated table. My goal is to use this, as well as a few similar flags, to create specific buckets for a waterfall chart. 

As a calculated table it returns all CustomerKeys as “IsNew”. I'm guessing this is related to being evaluated for a specific row context? I'm struggling to understand how to adapt this for a calculated table.

TEST Flag = 
VAR maxCurrentMonth = MAX(BoB[ReportingDate])
VAR minCurrentMonth = DATE(YEAR(maxCurrentMonth),MONTH(maxCurrentMonth),1)
VAR maxPriorYear = EOMONTH(maxCurrentMonth,-12)
VAR minPriorYear = DATE(YEAR(maxPriorYear),MONTH(maxPriorYear),1)
VAR new = CALCULATETABLE( DISTINCT(BoB[CustomerKey]), Dates[Date] <= maxCurrentMonth && Dates[Date] >= minCurrentMonth )
VAR old = CALCULATETABLE( DISTINCT(BoB[CustomerKey]), Dates[Date] <= maxPriorYear && Dates[Date] >= minPriorYear)
VAR IsNew = EXCEPT(new,old)
VAR IsLost = EXCEPT(old,new)
VAR selectedrow = SELECTEDVALUE(BoB[CustomerKey])

VAR lostfiltered = FILTER(IsLost, selectedrow  IN IsLost)
VAR newfiltered = FILTER(IsNew, selectedrow IN IsNew)

VAR flag =
    SWITCH(TRUE(),
        COUNTROWS(IsNew) > COUNTROWS(newfiltered), "IsNew",
        COUNTROWS(IsLost) > COUNTROWS(lostfiltered) , "IsLost",
        "IsRetained"
    )
RETURN

ADDCOLUMNS(
    VALUES(BoB[CustomerKey]),
    "Customer Flag", flag)

[Greg_Deckler]

@nbs333 I would avoid the TI functions personally. Something like:

CustomerFlag = 
VAR maxCurrentMonth = MAX(BoB[ReportingDate])
VAR minCurrentMonth = DATE(YEAR(maxCurrentMonth),MONTH(maxCurrentMonth),1)
VAR maxPriorYear = EOMONTH(latestDate,-12)
VAR minPriorYear = DATE(YEAR(maxPriorYear),MONTH(maxPriorYear),1)
VAR new = CALCULATETABLE( DISTINCT(BoB[CustomerKey]), Dates[Date] <= maxCurrentMonth && Dates[Date] >= minCurrentMonth )
VAR old = CALCULATETABLE( DISTINCT(BoB[CustomerKey]), Dates[Date] <= maxPriorYear && Dates[Date] >= minPriorYear)
VAR IsNew = EXCEPT(new,old)
VAR IsLost = EXCEPT(old,new)
VAR selectedrow = SELECTEDVALUE(BoB[CustomerKey])

VAR lostfiltered = FILTER(IsLost, selectedrow  IN IsLost)
VAR newfiltered = FILTER(IsNew, selectedrow IN IsNew)

VAR flag =
    SWITCH(TRUE(),
        COUNTROWS(IsNew) > COUNTROWS(newfiltered), "IsNew",
        COUNTROWS(IsLost) > COUNTROWS(lostfiltered) , "IsLost",
        "IsRetained"
    )
RETURN
flag