Count of earliest date by subgroup

[Anonymous]

Hello,

I have a table that I created using summarizecolumns that lists customers, Order dates and order type.

I am trying to create a line chart week over week showing a count of the first order for active customers on one line and then a second line that counts the number of customers that closed their accounts on each date.

I have everything working except the ability to select the first order for active customers. The data would look something like this. I am thinking the approach I should take is to somehow select the rows for plotting. But can’t seem to work it out in my head how to get that part to work. If there is a better approach that I should take (such as creating a measure table or something else), I am open.

Can someone please assist?

Customer ID	Order date	Order Type
1111	1/7/2019	Active
1212	12/3/2018	Active
1313	9/30/2019	Active
1111	12/30/2019	Active
1212	12/30/2018	Active
1212	6/15/2018	Active
1414	1/7/2019	Closed
1515	9/30/2018	Closed
1616	1/14/2019	Closed
1111	6/15/2018	Active

On the table below , is what I am thinking that I would somehow need to generate for selection to plot on a chart

Customer ID	Order date	Order Type	Select
1111	1/7/2019	Active
1212	12/3/2018	Active
1313	9/30/2019	Active	Line 1
1111	12/30/2019	Active
1212	12/30/2018	Active
1212	6/15/2018	Active	Line 1
1414	1/7/2019	Closed	Line 2
1515	9/30/2018	Closed	Line 2
1616	1/14/2019	Closed	Line 2
1111	6/15/2018	Active	Line 1

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can use below measure formula to achieve your requirement:

Measure =
CALCULATE (
    COUNT ( Table1[Customer ID] ),
    FILTER ( ALLSELECTED ( Table1 ), [Order date] < MAX ( Table1[Order date] ) ),
    VALUES ( Table1[Order Type] )
)

Regards,

Xiaoxin Sheng