Group by and show earliest date

[Anonymous]

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!

[az38]

@Anonymous 

my above solution returns you result that you asked for

[az38]

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

[Anonymous]

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.

[Anonymous]

Hi @Anonymous 
unfortunately it didn't work out for me

[Anonymous]

Hi @Anonymous  Have you tried the solution which i have suggested?

Thanks,

Pravin

[Anonymous]

Could you please share what output you are getting after table creation?

Thanks,

Pravin