First purchase and accumulated purchase by sales man

DAX DAX Calculations
1

Hi,

image
image738×460 17.6 KB

I would like to seek for help that the first purchase for each new customers for particular month which closed by sales man and accumulated sales closed by sales man.

Kindly assist.

Thank you.

2

Hi @dennistgc

I think you can use Sam’s formula. You only need to add a slicer for the salesman.

First PRODUCT Purchased =
MAXX (
    TOPN (
        1,
        SUMMARIZE ( Sales, Dates[Date],'Product'[Product Name] ),
        CALCULATE ( MIN ( Dates[Date] ) ),
        ASC
    ),
  'Product'[Product Name]
)

Sam
Sam1054×600 62.5 KB

I hope it helps,

Diego

3

Hi @dennistgc, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

4

Hi Diego,

Thanks for the reply. the salesman will not be in the slicer, it will be in the axis of a visual to show the performance for each salesman.

Thank you.

5

Hi Diego,

The DAX formula was not workable after few round testing as the result not as per expected. The expected result should be new client for the salesman, but the result was client for the salesman in every month. The old client will also appear in the visual.

Any idea?

Thank you.

6

Hi all,

I quote an example here.

Invoice table
|Salesman|Customers|posdate|Amount|
|George|ABC PLT|29-Sep-19| 14,386.00 |
|George|ABC PLT|14-Dec-19| 1,000.00 |
|George|ABC PLT|12-Jan-20| 400.00 |
|George|ABC PLT|10-May-20| 668.00|

Marketing table
|Salesman|Customers|Date of followup|
|George|ABC PLT|03-08-19|
|George|CED PLT|08-09-19|

from the above example,

  1. I had a time slicer which is month and year. When I select Month = May and Year = 20, it will not appear any for George as George customer name ABC PLT first purchase date on 29 Sep 2019. When I select Month = Sep and Year 2019, it will show 14,386 as the first purchase.

  2. 2nd visual would be the accumulated sales for George from the first date of purchase to current of ABC PLT.

Thank you.

7

Hi @dennistgc. Can you please provide an Excel mock-up of your expected results, along with the PBIX you’re using to present this sample data? We can then dive deeper and hopefully match your expected outcome. Greg

8

Hi @dennistgc

The following measure works fine for me.

New Customers =
CALCULATE (
COUNTROWS ( VALUES ( Customers ) ),
FILTER (
Customers,
CALCULATE (
[Total Sales],
FILTER (
ALLSELECTED ( ‘Calendar’ ),
‘Calendar’[Date] < MIN ( ‘Calendar’[Date] )
)
) = 0
),
FILTER ( Customers, [Total Sales] > 0 )
)

Hope it works for you,
Diego