cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Grouping records and selecting oldest

I have the following problem. I have a table that containts the following fields

<date>, <offer code>, <status>. <comment>

<date> = obvioys
<offer code> = a unique code for a customer offer
<status> = the status of the offer, for example "QUALIFIED", "OFFER", "WON", "CONTRACTS" etc.

Now, I have the following two records

1/feb/2017, 123, WON, "customer informed we got the job"

2/feb/2017, 123, WON, "customer asked some clarifications before starting contract negotiation"

these are two records for the offer 123 at two different dates. I want to build a report whereby I report when was the earliest date that an offer appears to be WON so that I can report the number and volume of business generate each month.

how do I select the earliest (date wise) record?

thnx

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous,

You can try to use below formula which used to calculate monthly count of order code:

Measure:

Monthly Count:
COUNTAX(FILTER(ALLSELECTED(Table), [Date]<=MAX([Date])&&Month([Date])=MONTH(MAX([Date]))&&[State]="Won"),[offer code])

In addition, you can use summarize function to summary the records and show the oldest records.

For example:

Summary Table = SUMMARIZE(FILTER(ALL('Table'),[State]="Won"),'Table'[Order Code],"MinDate",MIN([Date]),"Count",COUNT('Table'[Order Code]))

Regards,

Xiaoxin sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Community Support

Hi @Anonymous,

You can try to use below formula which used to calculate monthly count of order code:

Measure:

Monthly Count:
COUNTAX(FILTER(ALLSELECTED(Table), [Date]<=MAX([Date])&&Month([Date])=MONTH(MAX([Date]))&&[State]="Won"),[offer code])

In addition, you can use summarize function to summary the records and show the oldest records.

For example:

Summary Table = SUMMARIZE(FILTER(ALL('Table'),[State]="Won"),'Table'[Order Code],"MinDate",MIN([Date]),"Count",COUNT('Table'[Order Code]))

Regards,

Xiaoxin sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

thanks

how would the "monthly code" be defined? will it be a new measure? a new table.

RE: summary table, this is probably what I need. I'll give it a try and update

BR

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors