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
jakeudy
Helper I
Helper I

Identifying sales made to old customers

I have a dataset of sales confirmations that refreshes daily as new sales are entered. I am trying to figure out a way to somehow distinguish sales that are entered for a customer whose last entered sale was 3 years ago or more, for the purpose of identifying customers who have been inactive but that are coming back into the picture. My dataset has a field called "Confirmation Date" and another field called "Customer Account". I use that customer account field to link this dataset to my customers table, which gives me more details about the customer that the sale was entered for. Is there some type of logical statement I could build out in a custom column that will give the results that I want? Any help would be appreciated. Thanks!

13 REPLIES 13
v-yiruan-msft
Community Support
Community Support

Hi @jakeudy,

Based on your description , I created the following sample data and the results you may want (see below screenshot ) . Could you please check if my understanding is correct or not ? There are 4 customers : A01 , A02 , A03 , and A04 . The last consumption of customer A01 was in 2020/2/1 , but the previous consumption date was three years ago (2016/4/13) . Customer A02 and A03 have consumption records in the past three years . Customer A04 had no consumption records of the past three years . So in this case , do you ultimately want to get customer A04 ? I'm not sure if my understanding is correct or not . Could you please provide some sample data and your expected result .

sample.JPG

Best Regards

Rena

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

CUSTOMERACCOUNTConfirmDate

0000533/25/2010 0:00
0000534/5/2010 0:00
0000538/2/2010 0:00
0000538/24/2010 0:00
0000539/22/2010 0:00
0000539/27/2010 0:00
00005310/24/2011 0:00
00005311/2/2011 0:00
0000535/17/2012 0:00
0000533/5/2013 0:00
0000534/2/2013 0:00
0000534/3/2013 0:00
0000534/4/2013 0:00
0000534/26/2013 0:00
0000534/29/2013 0:00
0000534/30/2013 0:00
0000535/15/2013 0:00
0000531/16/2014 0:00
0000531/30/2014 0:00
0000533/5/2014 0:00
0000533/18/2014 0:00
0000533/19/2014 0:00
0000533/21/2014 0:00
0000535/6/2014 0:00
0000535/7/2014 0:00
0000537/11/2014 0:00
0000555/4/2010 0:00
0000555/5/2010 0:00
0000555/6/2010 0:00
0000647/28/2010 0:00
0000648/3/2010 0:00
0000661/29/2010 0:00
0000661/30/2010 0:00
0000662/1/2010 0:00
0000662/2/2010 0:00
0000665/14/2010 0:00
0000666/10/2010 0:00
00006611/22/2010 0:00
0000663/10/2011 0:00
0000666/23/2011 0:00

 

Here is some sample data. It looks exactly like what you posted in your second screenshot. And yes, that is exactly the result I'm trying to achieve. How did you do that? @v-yiruan-msft 

Hi @jakeudy ,

You can create one calculated column as below:

OldCustomers =
CALCULATE (
    MAX ( 'Sales'[Customer Account] ),
    FILTER (
        SUMMARIZE (
            'Sales',
            'Sales'[Customer Account],
            "CustAcc", ( 'Sales'[Customer Account] ),
            "ConL", CALCULATE (
                MAX ( 'Sales'[Confirm Date] ),
                ALLEXCEPT ( 'Sales', 'Sales'[Customer Account] )
            )
        ),
        DATEDIFF ( [ConL], TODAY (), YEAR ) >= 3
    )
)

Best Regards

Rena

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

Referring to your first reply where you attached screenshots of sample data, is there any way to manipulate this formula so that not only will it capture customer A04 in the "Old Customers" classification, but it will also capture customer A01. I want to set up a weekly report that shows how many customers were sold to in that week that had not been sold to in at least 3 years. So in your sample data, I would want customer A01 to show up in that report, theoretically. I have a page level filter set up that shows only sales confirmed in the current week. Somehow I need a way to sift through each customer sold to in that week and flag the ones that had been inactive for at least 3 years prior to the sale made to them in the current week. Does that make sense? @v-yiruan-msft 

Hi @jakeudy ,

Please update the previous formula with below one:

OldCustomers = var a =max('Sales'[Confirm Date])
var b=CALCULATE(max('Sales'[Confirm Date]),'Sales'[Confirm Date]<a)
var c=CALCULATE (
    MAX ( 'Sales'[Customer Account] ),
    FILTER (
        SUMMARIZE(DISTINCT('Sales'[Customer Account]),'Sales'[Customer Account],"ConL",CALCULATE(MAX('Sales'[Confirm Date]),FILTER('Sales','Sales'[Confirm Date]=b))),
    DATEDIFF ( [ConL], TODAY (), YEAR ) >= 3
    )
)
return c

old customer.JPG

Best Regards

Rena

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

So it seems like this formula is just filtering out all sales made within the last 3 yearssample data.PNG

The problem with this is that it doesn't really allow me to track old customers sold to each week. So for example, in reference to your sample data, lets say I make a sale of $7,000 to customer A04 on 3/15/2020. Since the previously most recent sale to that customer was 10/3/2016, I would want the sale of $7,000 on 3/15/2020 to show up on my weekly report of sales made to old customers. Conversely, if I made a sale of $10,000 to customer A03 on 3/16/2020 I would not want that sale to show up on this weekly report, because customer A03 was last sold to on 12/4/2018 which is within the last 3 years @v-yiruan-msft 

Hi @jakeudy ,

You can create one measure as below:

LineAmountB = IF (
    DATEDIFF (
        CALCULATE (
            MAX ( Sales[Latest ConfirmDate] ),
            FILTER (
                ALLSELECTED ( Sales ),
                Sales[Custaccount] = MAX ( Sales[Custaccount] )
                    && Sales[Latest ConfirmDate] < MAX ( Sales[Latest ConfirmDate] )
            )
        ),
       TODAY(),
        YEAR
    ) >= 3,
    CALCULATE (
        SUM ( Sales[LineAmountBooked] ),
        FILTER ( Sales, Sales[Latest ConfirmDate] = MAX ( Sales[Latest ConfirmDate] ) )
    )
)

Best Regards

Rena

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

I tried this new formula and it left me with a blank table. I have posted a screenshot for your reference. Part of the problem might be that my custaccount column comes from a different table as my confirm date and lineamountbooked columns. As you can see, my custaccount comes from vwSalesOrderHeaders and the other columns come from AX2009_SalesBookings. These two tables are linked through a column with unique Sales ID#s. So with that being said, I had to make some adjustments to the formula you posted. Also the "Latest ConfirmDate" was giving me an error message so I took out the word "Latest" and it was ok. Maybe those adjustments I made is what messed it up? @v-yiruan-msft 

sample data2.PNG

Hi @jakeudy ,

Could you please provide your PBIX file(exclude the sensitive data) if it is convenient?

 

Best Regards

Rena

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

Hi @jakeudy ,

The formula is applied by creating measure NOT calculated column, and I just updated it base on your scenario as below:

LineAmountB = IF (
    DATEDIFF (
        CALCULATE (
            MAX ( AX2009_SalesBookings[ConfirmDate] ),
            FILTER (
                ALLSELECTED ( 'AX2009_SalesBookings' ),
                AX2009_SalesBookings[SalesId_Key]= MAX (  AX2009_SalesBookings[SalesId_Key] )
                    && AX2009_SalesBookings[ConfirmDate] < MAX ( AX2009_SalesBookings[ConfirmDate] )
            )
        ),
       TODAY(),
        YEAR
    ) >= 3,
    CALCULATE (
        SUM ( AX2009_SalesBookings[LineAmountBooked] ),
        FILTER ( AX2009_SalesBookings, AX2009_SalesBookings[ConfirmDate] = MAX ( AX2009_SalesBookings[ConfirmDate] ) )
    )
)

old customers2.JPG

Best Regards

Rena

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

example.PNG

This is how I want to be able to set up my dashboard, but rather than showing sales made to new customers, I want it to show sales to customers that haven't been sold to in three years. That measure is just filtering out the last three years of sales data, which is not my intended result. From my sample file, it shows that the last sale made to customer 4550 was in 2010. Therefore, if i make a sale to customer 4550 this week I want that sale to show up on this dashboard in my table titled "weekly new customer sales". My cards on the far right are to keep track of the distinct count of old customer accounts sold to in the current week, month and year. So if my sale to customer 4550 this week was the only sale ive made to an old customer this entire year, then the distinct count would be "1" on each of those cards and it should show the dollar value of that sale as well @v-yiruan-msft 

Hi @jakeudy ,

You can create one measure as below:

LineAmountB = IF (
    DATEDIFF (
        CALCULATE (
            MAX ( Sales[Latest ConfirmDate] ),
            FILTER (
                ALLSELECTED ( Sales ),
                Sales[Custaccount] = MAX ( Sales[Custaccount] )
                    && Sales[Latest ConfirmDate] < MAX ( Sales[Latest ConfirmDate] )
            )
        ),
       TODAY(),
        YEAR
    ) >= 3,
    CALCULATE (
        SUM ( Sales[LineAmountBooked] ),
        FILTER ( Sales, Sales[Latest ConfirmDate] = MAX ( Sales[Latest ConfirmDate] ) )
    )
)

old customers.JPG

Best Regards

Rena

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

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.