Compare different rows and columns

[kasife]

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.

[danextian]

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 )

[kasife]

@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?

[danextian]

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?

[kasife]

@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 

[danextian]

Hi @kasife the link is private

[kasife]

@danextian 

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