Calculate difference between rows by group

[Unknown]

Hello,

I have a table with four columns: index, company, number, date (in the format DD/MM/YYYY).

The table looks like the one in the picture bellow.

I want to calculate the difference between the dates where the column number is not empty for each company.
I managed to create the column datediff (in yellow) with the following dax code:

     IF( Table[number]<>"" && CALCULATE( MAX(Table[date]), FILTER(Table, Table[company]=EARLIER(Table[company]) &&                                Table[number]<>"" && Table[date]<EARLIER(Table[date])))<> BLANK(),

        Table[date] - CALCULATE(MAX(Table[date]), FILTER(Jobs, Table[company]=EARLIER(Table[company]) &&                                                   Table[number]<>"" && Table[date]<EARLIER(Table[date]))))

However, I want to get the results in the column datediff_correct (in green). Which means, when for two consecutive indexes where number is not empty, if the date is the same it should return either blank or 0 (it will work the same for the problem).

Do you have any idea how to solve this? Thank you

[v-mengzhu-msft · Accepted solution]

Hi @Unknown ,

Thank you for the sample data, I created the same data as yours to test it, and finally succeeded in calculating the result you want to achieve. As the following shows, it is my testing result:

The measure I use is as follows, you can try it:

_date =

if(SELECTEDVALUE('Table'[number])="", BLANK(), DATEDIFF(

CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[company]=MAX('Table'[company])&&'Table'[Index]<MAX('Table'[Index])&&'Table'[number]<>"")),SELECTEDVALUE('Table'[date]),DAY)

)

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Unknown , In place of date use the index in your formula

[Unknown]

I've tried that, but for some reason it returns this message: "There's not enough memory to complete this operation. Please try again later when there may be more memory available."
I've tried calculating in different occasions but it always returns the same message