Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated column to indicate last year customer

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.

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

a1.png

 

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:

a2.png

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

a1.png

 

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:

a2.png

 

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Hi,

Please share some data to work with.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.