Number of Days between the same products

[Anonymous]

Hi There,

I am trying to find the number of days between a same product being applied on a same field on the basis of application dates. Whenever a product is applied on the field, the date is recorded as Application Date. 

The report looks like:

and the measure I am using to calculate is:

No. Of Days =

CALCULATE(

  DATEDIFF(

    MIN('Table'[ApplicationDate]),

    MAX('Table'[ApplicationDate]), DAY

  ),

  ALLEXCEPT(

    'Table', 'Table'[Field], 'Table'[Product], 'Table'[Type]

  )

)

But this gives me the no. of days between the first and last application dates. What I am looking for is to caclculate no. of days between previous and next application dates.
As per the 1st and 2nd record in the report, No. of days should be 7 days (Previous date :17th June 2021 and Next date: 24 June 2021.
How to achieve this?

Appreciate your help.

[rohit_singh · Accepted solution]

Hi @Anonymous ,

Not sure is this is what you need but you can try creating this measure :

Days since last application =

var _currdate = max(Products[Application Date])

var _nextdate =

CALCULATE(

max(Products[Application Date]),

FILTER(ALLEXCEPT(Products, Products[Type], Products[Product]), Products[Application Date] < _currdate

)

)

var _days =

CALCULATE(

DATEDIFF(_nextdate,_currdate,DAY))

RETURN

if(isblank(_days), 0, _days)

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[Anonymous]

It works as expected. Thank you so much for the quick solution.

[johnt75]

You can try

No of days =
VAR currentDate =
    SELECTEDVALUE ( 'Table'[Application date] )
VAR nextDate =
    SELECTCOLUMNS (
        CALCULATETABLE (
            TOPN ( 1, 'Table', 'Table'[Application date], ASC ),
            ALLEXCEPT ( 'Table', 'Table'[Field], 'Table'[Product], 'Table'[Type] ),
            'Table'[Application date] > currentDate
        ),
        "@val", 'Table'[Application date]
    )
RETURN
    DATEDIFF ( currentDate, nextDate, DAY )

[Anonymous]

I tried but it was giving me insufficient memory error.