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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors