Forum Discussion
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
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 )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?
