Difference between application dates of products in a given location.

[jobf]

Hello. Please take a look at the table below:

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

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

Location	Period	Application	Date	Difference
P1	1st	1st application	01/01/2025
P1	2nd	1st application	01/02/2025
P1	1st	2nd application	01/20/2025	19
P1	2nd	2nd application	01/21/2025	19
P2	1st	1st application	01/05/2025
P2	2nd	1st application	01/06/2025
P2	1st	2nd application	01/15/2025	10
P2	2nd	2nd application	01/19/2025	13

If this is the first application, it should show a null value. One thing to keep in mind is that this table is just an example. Sometimes, there will be 12 or more applications in the same location.

[Bibiano_Geraldo · Accepted solution]

Hi @jobf ,
you can achieve your goal by this DAX measure:

Difference = 
VAR CurrentDate = MAX('Table'[Date])
VAR CurrentApplication = SELECTEDVALUE('Table'[Application])
VAR Location = SELECTEDVALUE('Table'[Location])
VAR PreviousApplicationMaxDate =
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALL('Table'),
            'Table'[Location] = Location &&
            'Table'[Application] <> CurrentApplication &&
            'Table'[Date] < CurrentDate
        )
    )
RETURN
IF(
    ISBLANK(PreviousApplicationMaxDate),
    BLANK(),
    DATEDIFF(PreviousApplicationMaxDate, CurrentDate, DAY)
)

 Your output will look like this:

[ThxAlot · Accepted solution]

Remember a rule of thumb, integer is the most preferrable data type in PBI, namely good for data compression and calculation as well. Turn columns Period and Application to integer.

[Bibiano_Geraldo · Accepted solution]

Hi @jobf ,
you can achieve your goal by this DAX measure:

Difference = 
VAR CurrentDate = MAX('Table'[Date])
VAR CurrentApplication = SELECTEDVALUE('Table'[Application])
VAR Location = SELECTEDVALUE('Table'[Location])
VAR PreviousApplicationMaxDate =
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALL('Table'),
            'Table'[Location] = Location &&
            'Table'[Application] <> CurrentApplication &&
            'Table'[Date] < CurrentDate
        )
    )
RETURN
IF(
    ISBLANK(PreviousApplicationMaxDate),
    BLANK(),
    DATEDIFF(PreviousApplicationMaxDate, CurrentDate, DAY)
)

 Your output will look like this: