topic Re: Group by and show earliest date in DAX Commands and Tips

Group by and show earliest date

Anonymous — Mon, 02 Mar 2020 10:33:35 GMT

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!

Re: Group by and show earliest date

Anonymous — Mon, 02 Mar 2020 10:59:23 GMT

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.

Re: Group by and show earliest date

az38 — Mon, 02 Mar 2020 11:01:37 GMT

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

Re: Group by and show earliest date

Anonymous — Mon, 02 Mar 2020 11:52:24 GMT

Hi Anonymous
unfortunately it didn't work out for me

Re: Group by and show earliest date

Anonymous — Mon, 02 Mar 2020 11:48:33 GMT

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

Thanks,

Pravin

Re: Group by and show earliest date

Anonymous — Mon, 02 Mar 2020 11:55:30 GMT

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

Thanks,

Pravin

Re: Group by and show earliest date

az38 — Mon, 02 Mar 2020 13:03:56 GMT

Anonymous

my above solution returns you result that you asked for