Forum Discussion
Group by and show earliest date
Hello,
I have the next table:
| ReminderRowID | ReminderID | ContractNr | ContractStartDate | ReminderDate | CustomerID |
| 1 | R1 | C1 | 1-1-2019 | 3-1-2019 | 1001 |
| 2 | R1 | C1 | 1-1-2019 | 3-1-2019 | 1001 |
| 3 | R2 | C2 | 3-1-2019 | 10-2-2019 | 1002 |
| 4 | R3 | C2 | 3-1-2019 | 11-3-2019 | 1002 |
| 5 | R3 | C2 | 3-1-2019 | 11-3-2019 | 1002 |
| 6 | R4 | C3 | 5-1-2019 | 10-4-2019 | 1003 |
| 7 | R5 | C3 | 5-1-2019 | 5-6-2019 | 1003 |
I would like to group by all unique contractnumbers in the table above and show the FIRST reminderdate. After that I would like to calculate the datediff between the contractstartdate and the first reminderdate.
So it will look like this:
| Contractnr | ContractStartDate | FIRST Reminderdate | DaysBetween |
| C1 | 1-1-2019 | 3-1-2019 | 2 |
| C2 | 3-1-2019 | 10-2-2019 | 38 |
| C3 | 5-1-2019 | 10-4-2019 | 95 |
Last of all, I would like to calculate the number of customers that have received their first reminder on a contract withing 30 days...
Could somebody help me fix this business case with DAX?
Thank you!
6 Replies
Hi Anonymous
New table=Summarize(table,table[ContractNr],table[ContractStartdate],table[Reminderdate],"Between",datediff(max(table[ContractStartdate]),max(table[Reminderdate]),Day))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.- az38
Community Champion
Hi Anonymous
try a measure
DaysBetween = var _firstReminder = CALCULATE(MIN('Table'[ReminderDate]),ALLEXCEPT('Table','Table'[ContractNr])) return datediff(selectedvalue('Table'[ContractStartDate]),_firstReminder,DAY)and measure t count customers within 30 days
COUNTCUSTOMER = calculate(DISTINCTCOUNT('Table'[CustomerID]),FILTER(ALL('Table'),[DaysBetween]<=30)) - az38
Anonymous
my above solution returns you result that you asked for
