Forum Discussion

learning_going's avatar
learning_going
Frequent Visitor
2 years ago
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

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    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.

     

4 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      learning_going
      Frequent Visitor
      2 years ago

      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's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        2 years ago

        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.