Forum Discussion
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.
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?
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.
5 Replies
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/339586.
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 - __PreviousThanks for the suggestion but unfortunately, I can't seem to work it out.
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
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.
