cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mlrossi Regular Visitor
Regular Visitor

Count of earliest date by subgroup

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 IDOrder dateOrder Type
11111/7/2019Active
121212/3/2018Active
13139/30/2019Active
111112/30/2019Active
121212/30/2018Active
12126/15/2018Active
14141/7/2019Closed
15159/30/2018Closed
16161/14/2019Closed
11116/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 IDOrder dateOrder TypeSelect
11111/7/2019Active 
121212/3/2018Active 
13139/30/2019ActiveLine 1
111112/30/2019Active 
121212/30/2018Active 
12126/15/2018ActiveLine 1
14141/7/2019ClosedLine 2
15159/30/2018ClosedLine 2
16161/14/2019ClosedLine 2
11116/15/2018ActiveLine 1
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count of earliest date by subgroup

Hi @mlrossi,

 

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

3.png

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

Re: Count of earliest date by subgroup

Hi @mlrossi,

 

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

3.png

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |