cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors