Forum Discussion
Compare different rows and columns
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 )
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?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?
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
