Forum Discussion
date gap between multiple item for occurrence in different month
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.
Super UserHi 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)
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.
what element do i need to add in, to allow filtering based on the text as criteria?
i tried a few workout, to get the gap by the lot no; cant seem to get the result i wanted
- Ritaf1983
I 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))RETURNIF(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."