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

Helper V

## Compare different rows and columns

Guys,
can anyone help me. I'm having a lot of trouble getting the result I want. I have a "new date" column which is the date my inventory will end. I need to compare with the column "Date" which is the beginning of my next project. I need to know the time in months when I finish my project and when the next one starts.
If the difference is greater than zero, I need to get the value of that date.

6 REPLIES 6
Super User

Hi @kasife ,

First there should be a way to determine the order of projects. Which comes first and which is next? Assuming, that this order is based on Date column, create an index column by ranking the dates.

``````Project Index =
RANKX ( Data, Data[Date],, ASC, SKIP )
``````

Once you have the index, you simply compare pick up the date from the row where index  is current +1

``````Next Date =
CALCULATE (
MAX ( Data[Date] ),
FILTER ( Data, Data[Project Index] = EARLIER ( Data[Project Index] ) + 1 )
)
``````

And then the next part is getting the difference in months.

``````Diff =
DATEDIFF ( Data[New Date], Data[Next Date], MONTH )``````

Please take note that DATEDIFF works this way: 31/1/23 to 1/2/23 is a month

You can use DAY instead of MONTH and just divide the result by 30 and round it to the nearest whole number

``````Diff =
ROUND ( DIVIDE ( DATEDIFF ( Data[New Date], Data[Next Date], DAY ), 30 ), 0 )
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Helper V

@danextian
Thank you very much. It worked out great here.
To be more organized, how would I go about sorting by date and city as well?

Super User

Can you please post  a sample data that can be copy-pasted? Not an image as in your original post. Also, why jump from 1 to 3 whereas the first items are from 1 through 3?

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Helper V

@danextianIt would be to do this order by date and city.
Here is the example file https://drive.google.com/file/d/1Z6A2sVG-ZMN58wQxjJ_rL8UbGOg34YlM/view?usp=sharing

Super User

Hi @kasife the link is private

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Helper V

https://ufile.io/v3l52w6g Now I think it will work

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.