Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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)
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |