Calculate the number of days between the last and the previous sale DAX

[SErgey-kuznetso]

What formula / measure can be used to calculate the number of days between the last and the last but one sale? There is the following table and it is necessary to calculate, for example, the number of days between sales of company1 on 10/21/2020 (last) and 12/31/19 (penultimate).

Or you can just suggest how to find the penultimate sale of the company. Calculate the number of days between I can myself.

[amitchandak · Accepted solution]

@SErgey-kuznetso , as new columns

max Date = maxx(filter(Table, [company] =earlier([company])),[Date])
second last = maxx(filter(Table, [company] =earlier([company]) && [Date] < earlier([max Date]) ),[Date])
diff = datediff([second last],[max Date],day)

or as measures

max Date = maxx(filter(allselected(Table), [company] =max([company])),[Date])
second last = maxx(filter(allselected(Table), [company] =max([company]) && [Date] < max([max Date]) ),[Date])
diff = datediff([second last],[max Date],day)

[Tahreem24]

@SErgey-kuznetso ,

Try out this Measure. I am also attaching screen shot with output:

Day =

VAR Min_ = CALCULATE(MIN(Tbl2[Date]),ALLEXCEPT(Tbl2,Tbl2[Category]))

VAR Max_ = CALCULATE(MAX(Tbl2[Date]),ALLEXCEPT(Tbl2,Tbl2[Category]))

RETURN DATEDIFF(Min_,Max_,DAY)

[manikumar34]

@SErgey-kuznetso , 

Not clear, use something like this.

DATEDIFF(DATE(2020,10,21),DATE(2019,12,31),DAY)

Regards, 

Manikumar