cancel
Showing results for
Did you mean:
New Member

## Return Date day beetween Penultimate an last Purchase record

I'm setting up a panel in PowerBI, where I have to identify activation! I have a Fat table with the customer / vendor columns / date sale / value: The same is already linked to a date table, I would like to calculate how many days runs between the last and last but one last sale by customers, in my case all dates are In a single column sale date. Could someone help me in the formula?

3 REPLIES 3
Super User III

## Re: Return Date day beetween Penultimate an last Purchase record

```Previous_Sales_Days =
VAR Current_Date =
MAX ( Sales_dates[DataBase] )
VAR Previous_Date =
MAXX (
FILTER ( ALL ( Sales_dates ); Sales_dates[DataBase] < Current_Date );
Sales_dates[DataBase]
)
RETURN
DATEDIFF ( Previous_Date; Current_Date; DAY )```

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Microsoft

## Re: Return Date day beetween Penultimate an last Purchase record

I would like to calculate how many days runs between the last and last but one last sale by customers, in my case all dates are In a single column sale date.

@MFelix, based on my understanding, ALLEXCEPT ( Table1, Table1[NameCliente] ) may be needed to calculate sales dates for each customer in this scenario.

@wagnerantunes84, could your try using the formula below to create a measure to see if it works?

```Previous_Sales_Days =
VAR lastPurchaseDate =
CALCULATE (
MAX ( Table1[DataBase] ),
ALLEXCEPT ( Table1, Table1[NameCliente] )
)
VAR penultimateDate =
MAXX (
FILTER (
ALLEXCEPT ( Table1, Table1[NameCliente] ),
Table1[DataBase] < Current_Date
),
Sales_dates[DataBase]
)
RETURN
DATEDIFF ( Previous_Date, Current_Date, DAY )
```

Just replace Table1 with your real table name.

Regards

New Member

## Re: Return Date day beetween Penultimate an last Purchase record

Hi my friend, this firt method dont work. but i am trying this new example ok,

I'll get back to you soon, thank you very much.

sory my english, i am from brazil!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors