Forum Discussion

jobf's avatar
jobf
Helper II
11 months ago
Solved

Difference between dates in the same column

I need a measure or column that calculates the difference between the most recent date and the start date of a company's production. For example:

BranchDate
401/01/2025
401/05/2025
501/02/2025
501/10/2025
401/20/2025
601/03/2025
501/15/2025
601/05/2025

 

In the above case, I need the difference between the most recent and oldest dates for each branch. For example, for branch 4, the result should be 19.

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    11 months ago

    Hi jobf - You can create a below measure to find the date diff. For each branch as per given scenerio

     

     

     

     

     

    Date Difference = 

    VAR MinDate = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Branch]))

    VAR MaxDate = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Branch]))

    RETURN

    DATEDIFF(MinDate, MaxDate, DAY)

     

    Hope this helps

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    11 months ago

    jobf In your case this should be:

    Measure =
  VAR __Branch = MAX( 'Table'[Branch] )
  VAR __MaxDate = MAXX( FILTER( ALL( 'Table' ), [Branch] = __Branch ), [Date] )
  VAR __MinDate = MINX( FILTER( ALL( 'Table' ), [Branch] = __Branch ), [Date] )
  VAR __Result = ( __MaxDate - __MinDate ) * 1.
RETURN
  __Result

     

    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 - __Previous ) * 1.

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    11 months ago

    Hi jobf - You can create a below measure to find the date diff. For each branch as per given scenerio

     

     

     

     

     

    Date Difference = 

    VAR MinDate = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Branch]))

    VAR MaxDate = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Branch]))

    RETURN

    DATEDIFF(MinDate, MaxDate, DAY)

     

    Hope this helps

  • Royel's avatar
    Royel
    Super User
    11 months ago

    Hi jobf  Here is another simple calculated column for your days difference 

    Days Difference = 
VAR MinDate = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Branch]))
VAR MaxDate = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Branch]))
RETURN 
(MaxDate - MinDate) * 1.0

    Results: 

    Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!