Days between multiple dates

[samc_26]

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! 😀

[FBergamaschi · Accepted solution]

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() )

RETURN

IF (

    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 thread

Want 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

[Shahid12523 · Accepted solution]

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 ) )

[samc_26]

Thank you so much, this worked perfectly!! Much appreciated 💪

[FBergamaschi · Accepted solution]

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() )

RETURN

IF (

    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 thread

Want 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

[johnt75]

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