cancel
Showing results for 
Search instead for 
Did you mean: 
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

 



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
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)



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.