Forum Discussion

samc_26's avatar
samc_26
Helper IV
11 months ago
Solved

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 da...
Need Help
  • FBergamaschi's avatar
    FBergamaschi
    11 months ago

     

    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's avatar
    Shahid12523
    11 months ago

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

johnt75's avatar
johnt75
Super User
11 months ago

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