Forum Discussion
Days between multiple dates
Hi all,
I have a table that is set up like the below (changed data for this!) and I'm trying to find out the number of days between each date so that I can calculate an average. This is how my data is laid out at present.
| Product | Order Date | Column I would like to create ( days in-between dates listed by product) |
| Notebook | 01/01/25 | 0 |
| Notebook | 18/02/25 | 48 |
| Notebook | 28/02/25 | 9 |
| Pencil | 28/02/25 | 0 |
| Pencil | 14/03/25 | 14 |
| Pencil | 22/04/25 | 39 |
| Eraser | 19/05/25 | 27 |
| Eraser | 21/06/25 | 38 |
What I would like is a way of showing individual days between each order so it would show something like what is in the third column. I have no idea if this is possible, I would be extremley grateful if anyone can work it out! I've managed to write a measure using DATEDIFF which shows me the days between the earliest and latest date but it's the time in between each order I need really.
Thank you for reading! 😀
I named the table Fact
Created a key calc column
key = 'Fact'[Order Date] & 'Fact'[Product]DAX code of the calc column Delta Days
Delta Days =VAR keyRow = 'Fact'[key]VAR Prevkey = MAXX ( FILTER ( VALUES( 'Fact'[key] ), 'Fact'[key] < keyRow ), 'Fact'[key] )VAR PrevDate = CALCULATE( SELECTEDVALUE( 'Fact'[Order Date] ), 'Fact'[key] = Prevkey, REMOVEFILTERS() )RETURNIF (NOT ISBLANK( Prevkey ),INT('Fact'[Order Date] - PrevDate))If this helped, please consider giving kudos and mark as a solution
mein replies or I'll lose your threadWant to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Days Between =
VAR CurrentDate = 'Table'[Order Date]
VAR PrevDate =
CALCULATE (
MAX ( 'Table'[Order Date] ),
FILTER (
'Table',
'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[Order Date] < EARLIER ( 'Table'[Order Date] )
)
)
RETURN
IF ( ISBLANK ( PrevDate ), 0, DATEDIFF ( PrevDate, CurrentDate, DAY ) )
4 Replies
I named the table Fact
Created a key calc column
key = 'Fact'[Order Date] & 'Fact'[Product]DAX code of the calc column Delta Days
Delta Days =VAR keyRow = 'Fact'[key]VAR Prevkey = MAXX ( FILTER ( VALUES( 'Fact'[key] ), 'Fact'[key] < keyRow ), 'Fact'[key] )VAR PrevDate = CALCULATE( SELECTEDVALUE( 'Fact'[Order Date] ), 'Fact'[key] = Prevkey, REMOVEFILTERS() )RETURNIF (NOT ISBLANK( Prevkey ),INT('Fact'[Order Date] - PrevDate))If this helped, please consider giving kudos and mark as a solution
mein replies or I'll lose your threadWant to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Days Between =
VAR CurrentDate = 'Table'[Order Date]
VAR PrevDate =
CALCULATE (
MAX ( 'Table'[Order Date] ),
FILTER (
'Table',
'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[Order Date] < EARLIER ( 'Table'[Order Date] )
)
)
RETURN
IF ( ISBLANK ( PrevDate ), 0, DATEDIFF ( PrevDate, CurrentDate, DAY ) )Thank you so much, this worked perfectly!! Much appreciated 💪
You could create a calculated column like
Days since previous order = VAR CurrentDate = 'Table'[Order Date] VAR PrevDate = SELECTCOLUMNS( OFFSET( 1, ALL( 'Table'[Product], 'Table'[Order Date] ), ORDERBY( 'Table'[Order Date], DESC ), PARTITIONBY( 'Table'[Product] ) ), 'Table'[Order Date] ) VAR Result = DATEDIFF( PrevDate, CurrentDate, DAY ) RETURN Result
