latest date in a new column

[amontenegro93]

hi,
first sorry for my rough english

i have a dim table with clients, like this:

id	name	last name
1	jon	doe
2	jane	deo

in my fact table i have purchase by those clients:

Client_id	date	item
1	24/12/2022	a1
2	25/12/2022	a2
1	26/12/2022	a3
2	27/12/2022	a4

what i need is a new column in the dim table with the least purchase date for each client, for example:

id	name	last name	least purchase date
1	jon	doe	26/12/2022
2	jane	deo	27/12/2022

i cant find the correct functions to do this so i really apriciate your help, thanks!

[Ashish_Mathur]

Hi,

To your Table visual, drag the fields from the client Table.  Write this measure and format it as a Date

Last date = max(Fact[Date])

Hope this helps. 

[adudani · Accepted solution]

Hi @amontenegro93 ,

For the last/ latest purchase date for each customer:

Create a measure using the following DAX:

last transaction date =

MAXX(

FILTER(
FactTable,

FactTable[Customer_ID]=FactTable[Customer_ID]

),

FactTable[DateColumn]

)

For the first purchase made by the customer, replace MAXX with MINX. ( Not sure which one is required).
With the relationship from the date table to the fact table, you can filter this measure with any customer id context.

If this does not meet your requirement: Check out this link. Firstdate/lastdate might be applicable.
(114) How To Calculate Days Since Last Purchase Using DAX In Power BI [2022 Update] - YouTube 

Please accept this solution if it solves your query.
Appreciate a thumbs up if it helped.

[amontenegro93]

it works, thank you!