cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate difference between rows by group

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:

IFTable[number]<>"" && CALCULATEMAX(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

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Super User

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

Frequent Visitor

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