Calculated column to indicate last year customer

[Anonymous]

Hi All,

I want to add a column in my sales table , which indicates if customer exist LAST YEAR then 1 else 0. 

Sales table is connect to Date Table.

Thanks a lot.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.The pbix file is attached in the end.

Table:

You may create a calculated column or a measure as below.

Calculated column:

Flag Column = 
var _count = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        [Customer]=EARLIER('Table'[Customer])&&
        YEAR([Date])=YEAR(TODAY())-1
    )
)
return
IF(
    ISBLANK(_count),
    0,
    1
)

Measure:

Flag Measure = 
var _count = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        [Customer]=SELECTEDVALUE('Table'[Customer])&&
        YEAR([Date])=YEAR(TODAY())-1
    )
)
return
IF(
    ISBLANK(_count),
    0,
    1
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Typically you should write a measure for this - not a calculated column formula.

[Anonymous]

@@Ashish_Mathu Can you share some relevant article.  I am trying to do the following

New_Customers_Value =

CALCULATE(

SUM(Sales[Value]),

FILTER(

VALUES( Customer[No_] ),

AND(

CALCULATE( COUNTROWS( Sales) ) > 0,

CALCULATE(

COUNTROWS( Sales),

FILTER(

ALL( 'Date'[FY YEAR] ),

'Date'[FY YEAR] < MIN( 'Date'[FY YEAR] )

)

) = 0

)

)

)

and Old Customer Sales =  [ Total Sales ] - New_Customers_Value

Now, I am not able to identify which are these new customer , therefore I am trying to add calculates column. 
Can you please share the relevant measure to identify new customer 

[Ashish_Mathur]

Hi,

Please share some data to work with.

[amitchandak]

@Anonymous , for the new customer, you can say the one who did not come last year and same this year. With a date table

YTD = calculate([Sales],datesytd('Date'[Date]))
LYTD = calculate([Sales],DATESyTD(DATEADD('Date'[Date],-1,MONTH)))	
Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([YTD]) && not(ISBLANK([LYTD])) , 1,BLANK()))
New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LYTD]) && not(ISBLANK([YTD])) ,1,BLANK()))
Retained Customer This Month = if(not(ISBLANK([YTD])) && not(ISBLANK([LYTD])) , 1,BLANK())

Check this blog. This based on MTD. You have to use Datesytd

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

[amitchandak]

@Anonymous , Try the column, one of the two should work

if(isblank(countx(filter(table, [customer] = earlier([customer]) && year([customer]) = year(earlier([customer]))-1),[customer])),0,1)

if(isblank(countx(filter(table, [customer] = earlier([customer]) && year([customer]) = year(earlier([customer]))-1),earlier([customer]))),0,1)