Forum Discussion
Solved
date gap between multiple item for occurrence in different month
hi all whats the formula to calculate the days gap between each of the entry? line1: nov 4 to nov 11 line2: nov11 to nov 27 line3: nov27 to Dec 1
Hi learning_going
The first step is to add an index column from PQ (after sorting the table by the column of the wanted date)after you have an index column you can use a formula like : (replace parameters to the yours)
Difference =VAR CurrentRowIndex = 'Orders'[Index]VAR CurrentOrderDate = 'Orders'[Order Date]VAR PreviousOrderDate =CALCULATE(MAX('Orders'[Order Date]),FILTER('Orders','Orders'[Index] < CurrentRowIndex))RETURNIF(ISBLANK(PreviousOrderDate),0,DATEDIFF(PreviousOrderDate, CurrentOrderDate, DAY))
Result :PBIX with the example is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Ritaf1983
Super User
Super UserI apologize, but unfortunately I'm having trouble understanding what exactly you're trying to do. Please provide an example of the data and the desired result.
I also understand that this is a new topic, so I recommend asking it separately as an independent post.
tried altering to this
Lot Difference =
VAR CurrentRowIndex = Sheet1[Index]
VAR CurrentOrderDate = Sheet1[< InvDate]
VAR mix = Sheet1[LotNo]
VAR country = Sheet1[Country]
VAR PreviousOrderDate =
CALCULATE(
MAX(Sheet1[< InvDate]),
FILTER(
Sheet1,
Sheet1[Index] < CurrentRowIndex && mix = Sheet1[LotNo] && Sheet1[Country] = country
)
)
RETURN
IF(
ISBLANK(PreviousOrderDate),
0,
DATEDIFF(PreviousOrderDate, CurrentOrderDate, DAY)
but i kept getting msg " There's not enough memory to complete this operation. Please try again later when there may be more memory available."
but i kept getting msg " There's not enough memory to complete this operation. Please try again later when there may be more memory available."