Delivery Days

[M_SBS_6]

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.

This is my current table. I do have records prior to 2024 but I'm only interested in what happens from 2024 onwards.

Name.  Status.  Date
Test1.    Nores.  03/01/2024
Test2.    Nores.  05/01/2024
Test3.    Delivery1 10/01/2024
Test4.    Delivery2  12/01/2024

This is my table with the additional column added.

Name.  Status.  Date.               Days
Test1.    Nores.  03/01/2024   (blank)
Test2.    Nores.  05/01/2024.       2
Test3.    Delivery1 10/01/2024.   5
Test4.    Delivery2 12/01/2024.   2

Any idea how I can go about doing this please?

[v-kaiyue-msft · Accepted solution]

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.

[Greg_Deckler]

@M_SBS_6 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[M_SBS_6]

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

[Greg_Deckler]

@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]

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.