date gap between multiple item for occurrence in different month

[learning_going]

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

 

[Ritaf1983 · Accepted solution]

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

        )

    )

RETURN

    IF(

        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.

[learning_going]

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]

Hi @learning_going 

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.

[learning_going]

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."