Calculation of measure/column- based on next date in a partition by case with multiple rows per date

[Shir___]

I have a table with customer id and order date (and various other fields).

Each order date and customer has multiple rows (see example).

In addition, I have in another column the date we stopped working with the customer.

I would like to calculate the # of customers who had their next order in under 6 months, and the number of customers who we stopped working with (left) 6 months or less after their order date.

See this table to better understand what should be counted

Appreciate the help!

[Ashish_Mathur]

Hi,

The next time you hare some data, share it in a format that can be pasted in an MS Excel file.  Sharing an image is useless.  These calculated column formulas work

Next order within 6 months? = if(ISBLANK(CALCULATE(MIN(Data[order date]),FILTER(Data,Data[Customer id]=EARLIER(Data[Customer id])&&Data[order date]>EARLIER(Data[order date])))),BLANK(),if(DATEDIFF(Data[order date],CALCULATE(MIN(Data[order date]),FILTER(Data,Data[Customer id]=EARLIER(Data[Customer id])&&Data[order date]>EARLIER(Data[order date]))),MONTH)<=6,1,BLANK()))

Column = if(ISBLANK(Data[Next order within 6 months?]),if(DATEDIFF(Data[order date],Data[date stopped working],MONTH)<=6&&DATEDIFF(Data[order date],Data[date stopped working],MONTH)>0,1,BLANK()),BLANK())

Hope this helps.