Date difference in a specific location and period

[jobf]

Hi. Please take a look at the table below:

Location	Year	Application	Date
P1	2024	1st application	04/01/2024
P1	2025	1st application	01/02/2025
P1	2024	2nd application	05/20/2024
P1	2025	2nd application	01/21/2025
P2	2024	1st application	05/05/2024
P2	2025	1st application	01/06/2025
P2	2024	2nd application	07/15/2024
P2	2025	2nd application	01/19/2025

I need a column or measure that shows the difference (days) between the current application of the year and the most recent date of the last application at a given location of the year. It would look something like this:

Location	Year	Application	Date	Diff
P1	2024	1st application	04/01/2024
P1	2025	1st application	01/02/2025
P1	2024	2nd application	05/20/2024	49
P1	2025	2nd application	01/21/2025	19
P2	2024	1st application	05/05/2024
P2	2025	1st application	01/06/2025
P2	2024	2nd application	07/15/2024	71
P2	2025	2nd application	01/19/2025	13

If this is the 1st application, it should show a null value. Sometimes, there will be 12 or more applications in the same location and year.

[v-kongfanf-msft]

Hi @jobf ,

Maybe you can try formula like below to create calculated column:

DateDiff = 
VAR CurrentDate = 'Table'[Date]
VAR PreviousDate = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            'Table',
            'Table'[Location] = EARLIER('Table'[Location]) &&
            'Table'[Year] = EARLIER('Table'[Year]) &&
            'Table'[Date] < EARLIER('Table'[Date])
        )
    )
RETURN
IF(
    ISBLANK(PreviousDate),
    BLANK(),
    DATEDIFF(PreviousDate, CurrentDate, DAY)
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

@jobf You could create a calculated column like the following:

Column = 
  VAR __Location = [Location]
  VAR __Year = [Year]
  VAR __Date = [Date]
  VAR __Table = FILTER( 'Table', [Location] = __Location && [Year] = __Year && [Date] < __Date )
  VAR __Previous = MAXX( __Table, [Date] )
  VAR __Result = ( __Date - __Previous ) * 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/3395....
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.