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

Shahid12523's avatar
Shahid12523
Community Champion
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 ) )

samc_26's avatar
samc_26
Helper IV
11 months ago

Thank you so much, this worked perfectly!! Much appreciated ðŸ’ª