Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

Delivery Days

Hi, I am trying to track the number of days for a delivery status. Below is my current table. I'd like to track the number of days between each date with the first date showing as a BLANK. Thi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi M_SBS_6 ,

     

    Greg_Deckler  provided a good solution. I have another method here. I hope it will be helpful to you.


    1. After sorting the dates in ascending order in power query, create an index column.


    2. Create a calculated column and write an expression.

    Column =
VAR _pre = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Index] = EARLIER('Table'[Index]) - 1))
RETURN
DATEDIFF(_pre,'Table'[Date],DAY)


    3. If you don’t need the index column, you can click the little eye next to the column name to hide it.

     

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago

Thanks for the suggestion but unfortunately, I can't seem to work it out. 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

M_SBS_6 PBIX is attached below signature:

Column = 
  VAR __Current = [Date]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < __Current),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Date])
  VAR __Result = IF(__PreviousDate = BLANK(), BLANK(), ( __Current - __Previous ) * 1.)
RETURN
  __Result
  • M_SBS_6's avatar
    M_SBS_6
    Helper V
    2 years ago

    Thank you. Mine still doesn't work though, my first row populates with a 1 and I can't understand why that is. Im using the exact code you sent.