Help for a novice / newbie please

[samoht103]

Hi, I am trying to find the first date, last date, number of orders and refunds for each company. I'm sure this is pretty simple, but any help is much appreciated. I have included a sample of ten records and the output I am seeking below. Thanks heaps 😊

Record_no	Company	Date	Type
1	ABC	1/05/2020	Sale
2	XYZ	2/05/2020	Sale
3	ABC123	3/05/2020	Refund
4	ABC	4/05/2020	Refund
5	XYZ	5/05/2020	Refund
6	ABC123	6/05/2020	Sale
7	ABC	7/05/2020	Sale
8	XYZ	8/05/2020	Refund
9	ABC123	9/05/2020	Sale
10	Abc	10/05/2020	Refund
11	ABC123	9/05/2020	Refund

Company Name	Total Number of Sales	Earliest Sale Date	Most Recent Sale Date	Total Number of Sales	First Refund Date	Most Recent Refund Date	Days Between 1st Sale Last Refund
ABC	2	1/05/2020	7/05/2020	2	4/05/2020	10/05/2020	9
XYZ	1	2/05/2020	2/05/2020	1	5/05/2020	8/05/2020	6
ABC123	2	6/05/2020	9/05/2020	2	3/05/2020	9/05/2020	3

[amitchandak · Accepted solution]

@samoht103 , Create measures like these

Total Number of Sales = calculate(count(table[Record_no]),	table[Type]="Sales")
Total Number of Refund = calculate(count(table[Record_no]),	table[Type]="Refund")

Earliest  Sales date = calculate(Min(table[Date]),	table[Type]="Sales")
Most Recent Sales date = calculate(max(table[Date]),	table[Type]="Sales")

Earliest  Refund date = calculate(Min(table[Date]),	table[Type]="Refund")
Most Recent Refund date = calculate(max(table[Date]),	table[Type]="Refund")

Days Between 1st Sale Last Refund = datediff([Earliest  Refund date],[Most Recent Refund date],Day)

[nahid3152]

HI,
You can use max & Min function for getting first date & last Date